Syed Umair Rizvi Syed Umair Rizvi -3 years ago 149
SQL Question

How can do a dynamic query in this scenario . sql 2005

I am trying to enter userids from the query to send them emails .
it does not work and gives me this error

Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 238 At
least one of the following parameters must be specified. "@recipients,
@copy_recipients, @blind_copy_recipients".

declare @bodymsg nvarchar(max)
select @bodymsg = '<font face="calibiri" size="4" >Dear Users</font><br><br>
<font face="calibiri" size="5" color="red">Please Explain the Exrta Locked Faxes</font><br><br>
<font face="calibiri" size="4" >Check the Last Hour Snapshot Details Attached.<br><br>

declare @users_fetched varchar(max)
set @users_fetched = 'SELECT distinct Locked_Faxes_Last_Hour_Snap.userid from Locked_Faxes_Last_Hour_Snap'

declare @recipients varchar(max)

@recipients = STUFF((SELECT ';' + concerned_staff.staff_email from concerned_staff where (concerned_staff.staff_id in ('@users_fetched'))
), 1, 1, '')

EXEC msdb.dbo.sp_send_dbmail

@recipients = @recipients ,

@body= @bodymsg ,
@subject = 'Alert !!! Locked Faxes Violation Last Hour Snaps' ,
@profile_name = 'Database Profile 1',
@query = 'use qtel select * from dbo.Locked_Faxes_Last_Hour_Snap' ,
@attach_query_result_as_file = 1,
@query_attachment_filename ='Locked_Faxes_Last_Hour_Snap.csv',
@query_result_separator =',',
@query_result_header =1,
@body_format ='HTML',
@importance= 'HIGH';

Answer Source

You don't need dynamic sql here. Just put your query for fetching users as a where condition IN sub-query, when you create recipients:

@recipients = STUFF((SELECT ';' + concerned_staff.staff_email 
                     from concerned_staff 
                     where concerned_staff.staff_id in 
                         SELECT Locked_Faxes_Last_Hour_Snap.userid 
                         from Locked_Faxes_Last_Hour_Snap
                     FOR XML PATH('')
                     ), 1, 1, '')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download