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

[vertigo262]

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

[vertigo262]

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

USE [ReefJunkies]
GO
/****** Object:  StoredProcedure [dbo].[Proc_MailQue]    Script Date: 11/17/2010 23:34:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_MailQue]

AS


   
DECLARE @EmailRecipient VARCHAR(50);
DECLARE db_cursor CURSOR FOR
SET @EmailRecipient = (SELECT ToAddress from dbo.MailQueue WHERE (HasSent=0));
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @EmailRecipient;
 
WHILE @@FETCH_STATUS=0
BEGIN
 

 
EXEC msdb.dbo.sp_send_dbmail @recipients=@EmailRecipient,
    @subject = 'Subject',
    @body = 'body',
    @body_format = 'HTML',
@profile_name='Reef Junkies';


UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)

FETCH NEXT FROM db_cursor INTO @EmailRecipient;
END
 
CLOSE db_cursor;
DEALLOCATE db_cursor;

DELETE FROM MailQueue WHERE DateStamp < GETDATE()- 90

[vertigo262]

errr update.

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

updated code

USE [ReefJunkies]
GO
/****** Object:  StoredProcedure [dbo].[Proc_MailQue]    Script Date: 11/17/2010 23:34:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_MailQue]

AS

SET NOCOUNT ON;

DECLARE @MailqueueID int;
DECLARE @FromName VARCHAR(max);
DECLARE @FromAddress VARCHAR(max);
DECLARE @ToName VARCHAR(max);
DECLARE @ToAddress VARCHAR(max);
DECLARE @Subject VARCHAR(max);
DECLARE @Body VARCHAR(max);
DECLARE @HasSent int;
DECLARE @DateStamp Datetime;


/*DECLARE db_cursor CURSOR FOR */
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
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @ToAddress;
 
WHILE @@FETCH_STATUS=0
BEGIN
 

 
EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
    @from_address = @FromAddress,
    @subject = @Subject,
    @body = @Body,
    @body_format = 'HTML',
@profile_name='Reef Junkies';


UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)

FETCH NEXT FROM db_cursor INTO @ToAddress;
END
 
CLOSE db_cursor;
DEALLOCATE db_cursor;

DELETE FROM MailQueue WHERE DateStamp < GETDATE()- 90
   
SET NOCOUNT OFF;

[vertigo262]

ok,, working code

USE [ReefJunkies]
GO
/****** Object:  StoredProcedure [dbo].[Proc_MailQue]    Script Date: 11/17/2010 23:34:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_MailQue]

AS

SET NOCOUNT ON;

DECLARE @MailqueueID int;
DECLARE @FromName VARCHAR(max);
DECLARE @FromAddress VARCHAR(max);
DECLARE @ToName VARCHAR(max);
DECLARE @ToAddress VARCHAR(max);
DECLARE @varSubject VARCHAR(max);
DECLARE @varBody VARCHAR(max);
DECLARE @HasSent int;
DECLARE @DateStamp Datetime;


DECLARE db_cursor CURSOR FOR
SELECT MailqueueID, FromName, FromAddress, ToName, ToAddress, Subject, Body, HasSent, DateStamp 
from dbo.MailQueue 
WHERE (HasSent=0) 
Order by MailQueueID asc

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @MailqueueID,@FromName,@FromAddress,@ToName,@ToAddress,@varSubject,@varBody,@HasSent,@DateStamp;
 
 
WHILE @@FETCH_STATUS=0
BEGIN

 

 
EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
    @from_address = @FromAddress,
    @subject = @varSubject,
    @body = @varBody,
    @body_format = 'HTML',
@profile_name='Reef Junkies';


UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)

FETCH NEXT FROM db_cursor INTO @MailqueueID,@FromName,@FromAddress,@ToName,@ToAddress,@varSubject,@varBody,@HasSent,@DateStamp ;
END
 
CLOSE db_cursor;
DEALLOCATE db_cursor;



DELETE FROM MailQueue WHERE DateStamp < GETDATE()- 90
   
SET NOCOUNT OFF;

[ck9663]

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

[vertigo262]

What would the code look like to check for errors?

[ck9663]

Here, this is a good read.

Good Luck!!!

~~ CK

[vertigo262]

Something like this?

Try

 
EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
    @from_address = @FromAddress,
    @subject = @varSubject,
    @body = @varBody,
    @body_format = 'HTML',
@profile_name='Reef Junkies';


UPDATE MailQueue SET HasSent = 1 WHERE (HasSent = 0) AND (MailqueueID = @MailqueueID)

Catch e as Exception
    
End Try

[ck9663]

Close enough.

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

Good Luck!!!

~~CK