May 29, 2011 at 9:20 pm
Hi:
We are using SQl server 2005 standard edition. I am trying to run a procedure and email the results to certain people only if the result of the procedure is not null. I am using the following code :
Execute PrimAbsentees
IF (Select @@rowcount) > 0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email',
@recipients = 'someone@domain.com',
@execute_query_database='DatabaseName',
@query = 'Execute primAbsentees',
@subject ='Absence Alert',
@attach_query_result_as_file = 1
ELSE
END
The above code is supposed to execute a stored procedure and if the returned number of rows more than 0 then email the result to certain people otherwise stop.
When I run the above code I get a syntax error. please advise.
Kind regard
May 29, 2011 at 9:55 pm
2 things are wrong....
If you use the "END" statement, it must be paired up with a "BEGIN" statement. In you code, you don't need the END statement, so remove it.
The ELSE statement has no code in it (in your example, this is because you don't need to do anything). The ELSE statement requires code to follow it. The ELSE statement is optional. In you case, since you don't have anything that needs to be done in IF the row count is <= 0, you do not need the else statement.
A minor point wrt the IF statement. You do not need to use a SELECT statement. You can simply say "IF @@rowcount > 0.
So, your code becomes...
Execute PrimAbsentees
IF @@rowcount > 0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email',
@recipients = 'someone@domain.com',
@execute_query_database='DatabaseName',
@query = 'Execute primAbsentees',
@subject ='Absence Alert',
@attach_query_result_as_file = 1
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply