Syed Umair Rizvi Syed Umair Rizvi - 9 months ago 41
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>
Thanks</font></end>'

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)

SELECT
@recipients = STUFF((SELECT ';' + concerned_staff.staff_email from concerned_staff where (concerned_staff.staff_id in ('@users_fetched'))
FOR XML PATH('')
), 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_no_padding=1,
@exclude_query_output=1,
@append_query_error=0,
@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:

SELECT 
@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