August 10, 2014 at 11:41 am
Hello Everyone-
I've the below query where I am referencing an employee table that has the following structure and has three employee records
IDFIRST_NAMEEMAIL_ADDR
123DONALD DONALD@GMAIL.COM
345MAYANK MAYANKMIN@GMAIL.COM
657MAYANK_NEWMAYANKCGG@GMAIL.COM
I'm passing name of this table (dbO.TD_EmployeeProfile_FinalV2) to get the counts from function FN_COUNT_ROWS (count is 3 as you can see above) so this while loop should run 3 times for all three employees. I'm having issues with statement "SELECT @EMP_ID" and "SELECT @EMAIL"
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
,@EMP_ID INT
SET @intFlag =1
SET @TABLE_NAME='dbO.TD_EmployeeProfile_FinalV2'
SELECT @INTFLAGMAX = ROW_COUNT FROM dbo.FN_COUNT_ROWS (@TABLE_NAME)AS X
WHILE @intFlag <= @INTFLAGMAX
BEGIN
SELECT @EMP_ID = ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) ROW_NUM FROM (@TABLE_NAME)) WHERE ROW_NUM=@intFlag
SELECT @EMAIL = EMAIL_ADDR FROM (SELECT EMAIL_ADDR, ID FROM (@TABLE_NAME)) WHERE ID=@EMP_ID
EXEC msdb.dbo.Sp_send_dbmail
@recipients =@email,
@subject = 'email',
@importance= 'high',
@body = 'test123'
SET @intFlag = @intFlag + 1
SET @intFlag = @intFlag + 1
END
GO
---ERRORS i'm getting
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ')'
August 10, 2014 at 11:53 am
You can't reference a table with a variable unless you use dynamic SQL.
SELECT EMAIL_ADDR, ID FROM (@TABLE_NAME) won't work.
Since the value dbO.TD_EmployeeProfile_FinalV2 is assigned directly, whydon't you use it in your query?
SELECT EMAIL_ADDR, ID FROM dbO.TD_EmployeeProfile_FinalV2
August 10, 2014 at 12:32 pm
Ok I replaced the table variable with the table name and getting following errors now. Can I not reference variable @EMP_ID and @intFlag in the query? Is that not allowed? what could be an alternative to this? I just want the query to run for each employee one at a time.
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
,@EMP_ID INT
SET @intFlag =1
SET @TABLE_NAME='dbO.TD_EmployeeProfile_FinalV2'
--SET @columnList = 'email_address, first_name, manager_name, course_name, course_ID, Month_Due, ID'
SELECT @INTFLAGMAX = ROW_COUNT FROM dbo.FN_COUNT_ROWS (@TABLE_NAME)AS X
WHILE @intFlag <= @INTFLAGMAX
BEGIN
SELECT @EMP_ID = ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) ROW_NUM FROM dbO.TD_EmployeeProfile_FinalV2) WHERE ROW_NUM=@intFlag
SELECT @EMAIL = EMAIL_ADDR FROM (SELECT EMAIL_ADDR, ID FROM dbO.TD_EmployeeProfile_FinalV2) WHERE ID=@EMP_ID
EXEC msdb.dbo.Sp_send_dbmail
@recipients =@email,
@subject = 'email',
@importance= 'high',
@body = 'test123'
SET @intFlag = @intFlag + 1
SET @intFlag = @intFlag + 1
END
GO
--Errors:
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'WHERE'.
August 10, 2014 at 12:41 pm
Both of your SLEECT statements between parenthesis ar missing the table alias at the end.
E.g.
SELECT @EMP_ID = ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) ROW_NUM FROM dbO.TD_EmployeeProfile_FinalV2) table1 WHERE ROW_NUM=@intFlag
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply