473,218 Members | 1,394 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 software developers and data experts.

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 6356
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

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

Similar topics

0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
3
by: Abdulla Herzallah | last post by:
Hi every one I have tried to search for any links, hints clues on how to create a Stored Procedure programmatically using C# but came back with empty handed :-( I know it is definitly possible...
11
by: harborboy76 | last post by:
Hi, I have a stored procedure that does a lot of INSERT/UDATE to 3 tables. And When I call the stored procedure, I get a Transaction Log Full error. When I want to do is turning off the...
0
by: usenet | last post by:
Hi. I have a DB2 stored procedure that I call using JDBC. Now I'm trying to batch the call. This is done by the book: stmt = connection.prepareCall(" ... "); stmt.setString(1, "foo");...
1
by: sowmyavenkat | last post by:
hi all, Iam working on a Maintainance and enhancement project. It was developed by somebody. Now there is a bug which is to be solved. Can any body help me to find out which stored procedure is...
0
by: ambikadevi | last post by:
What is the function to call the Postgres stored procedures function using ADODB? PrepareSP() function is used to call the Oracle Stored Procedure. For PHP application. Waiting for the response!
3
by: ckauvar | last post by:
The PHP below calls a stored procedure in a MSSQL database when I am using SQL in a Windows environment. I've recently switched to a UNIX environment and am now using ODBC (via FreeTDS) to connect...
4
by: Dooza | last post by:
I have a stored procedure that needs to be executed via an ASP page. The SP does a number of tasks, mainly comparing a local table against a linked server table, and updating/inserting/deleting...
2
by: gjain12 | last post by:
When I am trying to declare a cursor over a temproay table in my stored procedure I am getting some errors. Below are the piece of code from my stored procedure in which I am getting error. ...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.