July 7, 2014 at 8:03 am
d
July 7, 2014 at 8:12 am
Krasavita (7/7/2014)
DECLARE @Emp_ID intDECLARE @Sheet_ID int
SELECT * INTO #TimesheetEMP
FROM (
SELECT @Emp_ID = Emp_ID
FROM Pay_Timesheets_Sheet
WHERE Sheet_ID = @Sheet_ID)timesheetemployee
I get error:incroect syntax near @Emp_ID = Emp_ID
Thank you
Well currently @Sheet_ID is set to NULL
Plus I'm not sure (don't have access to SQL at the moment to test) trying to set the @Emp_ID value inside of a Select INTO will work right.
I would switch it to something like this
Declare @Sheet_ID int
Declare @Emp_ID int
Create Table #TimesheetEMP (EMPID int)
Insert Into #TimesheetEMP
(Select EMP_ID
FROM Pay_Timesheets_Sheet
WHERE Sheet_ID = @Sheet_ID)
--NOTE this will only insert EMP_ID's where Sheet_ID is null since @Sheet_ID is currently set to NULL
July 7, 2014 at 8:14 am
The "SELECT @Emp_ID = Emp_ID" part assigns a value to a variable and does not return any records. Therefor it can't be used to insert records into a (temporary) table.
You need to seperate the queries:
DECLARE @Emp_ID int
DECLARE @Sheet_ID int
SELECT @Sheet_ID = xxx -- assign your value to the variable ...
/*
...and insert the related Emp_ID into the temporary table
*/
SELECT Emp_ID
INTO #TimesheetEMP
FROM Pay_Timesheets_Sheet
WHERE Sheet_ID = @Sheet_ID
/*
or if you only need to assign the EMP_ID to the variable (no need to add to temporary table)
*/
SELECT @Emp_ID = Emp_ID
FROM Pay_Timesheets_Sheet
WHERE Sheet_ID = @Sheet_ID
July 7, 2014 at 8:21 am
Chord77 (7/7/2014)
--NOTE this will only insert EMP_ID's where Sheet_ID is null since @Sheet_ID is currently set to NULL
NO. This will not return rows where Sheet_ID currently set to NULL. This is fundamental to working with NULL in sql server.
The following code is what would be run when the variable is not set. This will NEVER return any row because there is no value of Sheet_ID that equals NULL.
Where Sheet_ID = NULL
If you want to return rows where Sheet_ID is NULL you would use this:
Where Sheet_IS IS NULL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 7, 2014 at 8:22 am
Thank you
July 8, 2014 at 6:16 am
Sean Lange (7/7/2014)
Chord77 (7/7/2014)
--NOTE this will only insert EMP_ID's where Sheet_ID is null since @Sheet_ID is currently set to NULL
NO. This will not return rows where Sheet_ID currently set to NULL. This is fundamental to working with NULL in sql server.
The following code is what would be run when the variable is not set. This will NEVER return any row because there is no value of Sheet_ID that equals NULL.
Where Sheet_ID = NULL
If you want to return rows where Sheet_ID is NULL you would use this:
Where Sheet_IS IS NULL
Thanks good catch.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply