CODEWITHSUNDEEP
t-sqlstored-proceduressql-server-2008-r2
d90
d90

Reputation: 787

Stored Procedure to Email multiple recipients from query

I've created a query that pulls info and emails in the way that I need it to be presented to our clients. How can I turn this into a stored procedure fed by a query (query A)? I need it to run for each unique set of cmp_code and cmp_e_mail it returns. So for example if QueryA returns the following, I need the email query to run for each of them individually. 

C0001 [email protected]
C0002 [email protected]
C0003 [email protected]

QueryA: 

SELECT DISTINCT 
       [cmp_code]
      ,[cmp_e_mail]
  FROM Table1

Query to email: 

DECLARE @email nvarchar(50)
DECLARE @cmp_code nvarchar (5)
DECLARE @profile nvarchar(50)
DECLARE @subject nvarchar(100)
DECLARE @querystr nvarchar (MAX)

set @email = QueryA.[cmp_e_mail]
set @cmp_code = QueryA.[cmp_code]
set @profile = 'Reports'
set @subject  =  'Test'+@cmp_code
set @querystr = 'SELECT  [Year],[Week],[DueDate]
  FROM Table1
  WHERE [cmp_code] = '''+@cmp_code+'''';


EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @email,
@subject = @subject,
@body = 'This message is to inform you that we have not received your financial report for the following weeks. 
Please remit as soon as possible and pay by the due date listed.', 
@query = @querystr

Upvotes: 0

Views: 434

Answers (1)

Rachael M
Rachael M

Reputation: 92

try create a stored procedure like this one below that loops through the table and then calls another stored procedure passing in the data , be sure to deallocate and close the cursor at the end

Declare @Code nvarchar(50)
Declare @EmailAddress nvarchar(Max)  
Declare dbCurSP Cursor

For SELECT DISTINCT  [cmp_code]  FROM Table1

Open dbCurSP

Fetch Next From dbCurSP Into @Code

While @@fetch_status = 0

Begin 
     -- find email address
     SELECT @EmailAddress= [cmp_e_mail] FROM Table1 where [cmp_code]=@Code

     execute SP_SendEmail @EmailAddress, @Code

    Fetch Next From dbCurSP Into @Code
End

Close dbCurSP
Deallocate dbCurSP

Upvotes: 1

Related Questions