Row by Row Stored Procedure in SQL Server 2008 R2

65 pts.
Tags:
SQL
SQL Server 2008
SQL Server 2008 R2
SQL stored procedures
Stored Procedures
Hi all,

Hopefully someone will be able to help me here - I'm nearly there but can't get it right, I'm trying to create a stored procedure that when it runs, it will do a query and then send send an email out for each of the rows that it returns.

e.g.

If I have a DB with 4 columns (ID, Days, Emailed, Name), I would like the query to do a search and find all the records that are below a certain number of days and the emailed column set at 0 - then for each of the rows returned, send an email with the name column in it - but then for each row sent by email, set the emailed column to 1.

If someone can help, then that would be great. Any more info need, just let me know.

Cheers in advance
Jason


Software/Hardware used:
SQL Server 2008 R2
ASKED: October 3, 2010  1:36 PM
UPDATED: October 25, 2013  7:46 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could do it using the sp_send_dbmail procedure.

Here’s a simplified example that should get you started:

CREATE PROCEDURE yourProc AS
DECLARE @name VARCHAR(50)
DECLARE @id INTEGER
DECLARE yourCursor CURSOR
FOR SELECT id,name
FROM yourTable
WHERE Days < 10 AND Emailed = 0;
OPEN yourCursor
FETCH NEXT FROM yourCursor INTO @id,@name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_send_dbmail @profile_name='YourProfile',
@recipients='your@recipients.com',
@subject='The subject',
@body=@name
UPDATE yourTable SET Emailed = 1 WHERE id = @id
FETCH NEXT FROM yourCursor INTO @id,@name
END
CLOSE yourCursor
DEALLOCATE yourCursor
GO

This may be helpful:

SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database.

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Turdywills
    Great, thanks for this Carlosdl, that combined with the little info i already had, i have it working a treat now - thankyou very much. Jason
    65 pointsBadges:
    report
  • Turdywills
    Actually, one more quick question - if i wanted to combine two columns [column1] + [column2] - where would i put it in this query - ideally i want to be able to combine two columns and use that in the @body section of the email - (where you have @body = @name) would it be after the first WHERE statement? Cheers again jason
    65 pointsBadges:
    report
  • carlosdl
    You could do it in the query definition, or when calling the sp_send_dbmail procedure (I would probably opt for the latter) Example 1:
    SELECT id, col_x + col_y AS email_body
    ...
    FETCH NEXT FROM yourCursor INTO @id,@email_body
    ...
        EXEC sp_send_dbmail @profile_name='YourProfile',
        @recipients='your@recipients.com',
        @subject='The subject',
        @body=@email_body
    ...
    
    Example 2:
    SELECT id, col_x , col_y
    ...
    FETCH NEXT FROM yourCursor INTO @id, @col_x, @col_y
        EXEC sp_send_dbmail @profile_name='YourProfile',
        @recipients='your@recipients.com',
        @subject='The subject',
        @body=@col_x + @col_y
    ...
    65,110 pointsBadges:
    report
  • Turdywills
    mm, thanks again Carlosdl, i did try that, and i get an incorrect syntax on the + symbol.....
    65 pointsBadges:
    report
  • carlosdl
    True. Try using an additional variable. Something like this:
    ...
    SELECT @mail_body = @col_x + @col_y
    ...
    and then use the new variable when executing sp_send_dbmail.
    65,110 pointsBadges:
    report
  • Turdywills
    Hi Carlosdl, soo frustrating as i'm nearly there( with a lot of your help) - i now get "variable assignment is not allowed in a cursor declaration" if i try to do it in the existing SELECT statement. - is cursor the best way to do it - i have heard people say to use the while statement rather than cursor - but i'm stuck either way. thanks again for all your help Carolsdl. Jason
    65 pointsBadges:
    report
  • carlosdl
    Can we take a look at your current code ? Can you post it ? Since you need to send an e-mail for each record, I can't think of a better way to do it, than using a cursor.
    65,110 pointsBadges:
    report
  • Turdywills
    Hi Carlosdl; here is the code that i am using:
    
    USE [DeviceRegistrations]
    GO
    /****** Object:  StoredProcedure [dbo].[expiring]    Script Date: 10/04/2010 16:51:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[expiring] AS
    		DECLARE @CompanyName nvarchar (50) = NULL
    		DECLARE @ProductKey nvarchar (50) = NULL
    		DECLARE @MaintenanceStartDate nvarchar (10) = NULL
    		DECLARE @MaintenanceEndDate nvarchar (10) = NULL
    		DECLARE @Emailed int = NULL
    		DECLARE @Subject nvarchar (max) = NULL
    		DECLARE @FullSubject nvarchar (max) = NULL
    		DECLARE @SalesManEmail nvarchar (max) = NULL
    		DECLARE @NumberOfDays int = NULL
    		DECLARE @SalesManFirstName nvarchar (50) = NULL
    		DECLARE @SalesManLastName nvarchar (50) = NULL
    		DECLARE @SalesMan nvarchar (max) = NULL
    		DECLARE @Body nvarchar (max) = NULL
    		DECLARE @RegID int = NULL 
    		DECLARE yourCursor CURSOR
         FOR 
         
    		SELECT	[RegistrationIndex],
    				[CompanyName],
    				[Product Key],
    				[Maintenance End Date],
    				[SalesManEmail],
    				[SalesManFirstName],
    				[SalesManLastName]	
         
         FROM RegistrationDetails 
         WHERE (datediff(d,GETDATE(),[eCopy Maintenance End Date]) < 30) AND Emailed = 0;
         
     OPEN yourCursor
     FETCH NEXT FROM yourCursor INTO	@RegID,
    									@CompanyName,
    									@ProductKey,
    									@MaintenanceEndDate,
    									@SalesManEmail,
    									@SalesManFirstName,
    									@SalesManLastName
     WHILE @@FETCH_STATUS = 0
     BEGIN
         EXEC msdb.dbo.sp_send_dbmail @profile_name='Jason',
    
    		@recipients = @SalesManEmail,
    		@subject = @CompanyName,
    		@body = @CompanyName,
    		@body_format = 'HTML';
    		
         UPDATE RegistrationDetails SET Emailed = 1 WHERE RegistrationIndex = @RegID
         FETCH NEXT FROM yourCursor INTO	@RegID,
    										@CompanyName,
    										@ProductKey,
    										@MaintenanceEndDate,
    										@SalesManEmail,
    										@SalesManFirstName,
    										@SalesManLastName
     END
     CLOSE yourCursor
     DEALLOCATE yourCursor
    
    i'd like to be able to put a string in the subject line + @CompanyName and to also use HTML in the body (not crucial) hope it all makes sense. Jason
    65 pointsBadges:
    report
  • carlosdl
    Hi Jason, Try something like this:
         SELECT @FullSubject = 'Something ' + @CompanyName,
                @FullBody = '<h1>' + @CompanyName + '</h1>'
    
         EXEC msdb.dbo.sp_send_dbmail @profile_name='Jason',
    		@recipients = @SalesManEmail,
    		@subject = @FullSubject,
    		@body = @FullBody,
    		@body_format = 'HTML';
    65,110 pointsBadges:
    report
  • Turdywills
    Ahh, thank you Caroldl, worked a treat, i wasn't sure where i could put it in the whole stored procedure. Thankyou for all your help on this. Much appreciated. jason
    65 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following