468,765 Members | 1,513 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,765 developers. It's quick & easy.

How to write a stored procedure that sends emails from db data and update when sent?

Hi,

Was wondering if anyone had a sample stored procedure code that relates to what I'm trying to do.

I have a SQL table called MailQueue

with fields

MailQueue
- FromName
- FromAddress
- ToName
- ToAddress
- Subject
- Body
- HasSent
- DateStamp

What I'm trying to do is write a stored procedure that will send all email's with HasSent = 0 using sp_send_dbmail

Once the email goes out, I need to do an update Query that changes HasSent = 1

finally, one more SQL query that deletes all emails with hasSent = 1 that is older then 3 months.

If anyone has any code that's similar, I'd greatly appreciate it
Nov 18 '10 #1
8 6192
OK, this is a mess, but it gives me something to work with. any help would be appreciated. I'm not very good with stored procedures

Expand|Select|Wrap|Line Numbers
  1.  
  2. USE [ReefJunkies]
  3. GO
  4. /****** Object:  StoredProcedure [dbo].[Proc_MailQue]    Script Date: 11/17/2010 23:34:07 ******/
  5. SET ANSI_NULLS ON
  6. GO
  7. SET QUOTED_IDENTIFIER ON
  8. GO
  9. ALTER PROCEDURE [dbo].[Proc_MailQue]
  10.  
  11. AS
  12.  
  13.  
  14.  
  15. DECLARE @EmailRecipient VARCHAR(50);
  16. DECLARE db_cursor CURSOR FOR
  17. SET @EmailRecipient = (SELECT ToAddress from dbo.MailQueue WHERE (HasSent=0));
  18. OPEN db_cursor;
  19. FETCH NEXT FROM db_cursor INTO @EmailRecipient;
  20.  
  21. WHILE @@FETCH_STATUS=0
  22. BEGIN
  23.  
  24.  
  25.  
  26. EXEC msdb.dbo.sp_send_dbmail @recipients=@EmailRecipient,
  27.     @subject = 'Subject',
  28.     @body = 'body',
  29.     @body_format = 'HTML',
  30. @profile_name='Reef Junkies';
  31.  
  32.  
  33. UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)
  34.  
  35. FETCH NEXT FROM db_cursor INTO @EmailRecipient;
  36. END
  37.  
  38. CLOSE db_cursor;
  39. DEALLOCATE db_cursor;
  40.  
  41. DELETE FROM MailQueue WHERE DateStamp < GETDATE()- 90
  42.  
  43.  
  44.  
  45.  
  46.  
  47.  
  48.  
  49.  
  50.  
  51.  
Nov 18 '10 #2
errr update.

I know I'm close, but not working yet.
also screwed up the curser which I don't really understand.

updated code

Expand|Select|Wrap|Line Numbers
  1. USE [ReefJunkies]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[Proc_MailQue]    Script Date: 11/17/2010 23:34:07 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER PROCEDURE [dbo].[Proc_MailQue]
  9.  
  10. AS
  11.  
  12. SET NOCOUNT ON;
  13.  
  14. DECLARE @MailqueueID int;
  15. DECLARE @FromName VARCHAR(max);
  16. DECLARE @FromAddress VARCHAR(max);
  17. DECLARE @ToName VARCHAR(max);
  18. DECLARE @ToAddress VARCHAR(max);
  19. DECLARE @Subject VARCHAR(max);
  20. DECLARE @Body VARCHAR(max);
  21. DECLARE @HasSent int;
  22. DECLARE @DateStamp Datetime;
  23.  
  24.  
  25. /*DECLARE db_cursor CURSOR FOR */
  26. SELECT @MailqueueID = MailqueueID, @FromName = FromName, @FromAddress = FromAddress, @ToName = ToName, @ToAddress = ToAddress, @Subject = Subject, @Body = Body, @HasSent = HasSent, @DateStamp = DateStamp from dbo.MailQueue WHERE (HasSent=0) Order by MailQueueID asc
  27. OPEN db_cursor;
  28. FETCH NEXT FROM db_cursor INTO @ToAddress;
  29.  
  30. WHILE @@FETCH_STATUS=0
  31. BEGIN
  32.  
  33.  
  34.  
  35. EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
  36.     @from_address = @FromAddress,
  37.     @subject = @Subject,
  38.     @body = @Body,
  39.     @body_format = 'HTML',
  40. @profile_name='Reef Junkies';
  41.  
  42.  
  43. UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)
  44.  
  45. FETCH NEXT FROM db_cursor INTO @ToAddress;
  46. END
  47.  
  48. CLOSE db_cursor;
  49. DEALLOCATE db_cursor;
  50.  
  51. DELETE FROM MailQueue WHERE DateStamp < GETDATE()- 90
  52.  
  53. SET NOCOUNT OFF;
  54.  
  55.  
  56.  
  57.  
  58.  
  59.  
  60.  
  61.  
Nov 18 '10 #3
ok,, working code

Expand|Select|Wrap|Line Numbers
  1. USE [ReefJunkies]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[Proc_MailQue]    Script Date: 11/17/2010 23:34:07 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER PROCEDURE [dbo].[Proc_MailQue]
  9.  
  10. AS
  11.  
  12. SET NOCOUNT ON;
  13.  
  14. DECLARE @MailqueueID int;
  15. DECLARE @FromName VARCHAR(max);
  16. DECLARE @FromAddress VARCHAR(max);
  17. DECLARE @ToName VARCHAR(max);
  18. DECLARE @ToAddress VARCHAR(max);
  19. DECLARE @varSubject VARCHAR(max);
  20. DECLARE @varBody VARCHAR(max);
  21. DECLARE @HasSent int;
  22. DECLARE @DateStamp Datetime;
  23.  
  24.  
  25. DECLARE db_cursor CURSOR FOR
  26. SELECT MailqueueID, FromName, FromAddress, ToName, ToAddress, Subject, Body, HasSent, DateStamp 
  27. from dbo.MailQueue 
  28. WHERE (HasSent=0) 
  29. Order by MailQueueID asc
  30.  
  31. OPEN db_cursor;
  32. FETCH NEXT FROM db_cursor INTO @MailqueueID,@FromName,@FromAddress,@ToName,@ToAddress,@varSubject,@varBody,@HasSent,@DateStamp;
  33.  
  34.  
  35. WHILE @@FETCH_STATUS=0
  36. BEGIN
  37.  
  38.  
  39.  
  40.  
  41. EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
  42.     @from_address = @FromAddress,
  43.     @subject = @varSubject,
  44.     @body = @varBody,
  45.     @body_format = 'HTML',
  46. @profile_name='Reef Junkies';
  47.  
  48.  
  49. UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)
  50.  
  51. FETCH NEXT FROM db_cursor INTO @MailqueueID,@FromName,@FromAddress,@ToName,@ToAddress,@varSubject,@varBody,@HasSent,@DateStamp ;
  52. END
  53.  
  54. CLOSE db_cursor;
  55. DEALLOCATE db_cursor;
  56.  
  57.  
  58.  
  59. DELETE FROM MailQueue WHERE DateStamp < GETDATE()- 90
  60.  
  61. SET NOCOUNT OFF;
  62.  
  63.  
  64.  
Nov 18 '10 #4
ck9663
2,878 Expert 2GB
Your code will work fine.

You may implement this without a cursor, just a WHILE LOOP. Also, you have to check for errors before you update the HasSent to 1. What if the Mail failed? Your table will say the email has been sent already even if it's not true.

Happy Coding!!!

~~ CK
Nov 18 '10 #5
What would the code look like to check for errors?
Nov 18 '10 #6
ck9663
2,878 Expert 2GB
Here, this is a good read.

Good Luck!!!

~~ CK
Nov 19 '10 #7
Something like this?

Expand|Select|Wrap|Line Numbers
  1.  
  2. Try
  3.  
  4.  
  5. EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
  6.     @from_address = @FromAddress,
  7.     @subject = @varSubject,
  8.     @body = @varBody,
  9.     @body_format = 'HTML',
  10. @profile_name='Reef Junkies';
  11.  
  12.  
  13. UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)
  14.  
  15. Catch e as Exception
  16.  
  17. End Try
  18.  
  19.  
Nov 20 '10 #8
ck9663
2,878 Expert 2GB
Close enough.

Here's the full syntax of the TRY..CATCH command.

Good Luck!!!

~~CK
Nov 22 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by harborboy76 | last post: by
3 posts views Thread by ckauvar | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.