Running A Stored Proceedure Using Variables In Table

Question

Running A Stored Proceedure Using Variables In Table

Alasdair Thomson

Hall of Fame

Points: 3630
More actions
July 15, 2008 at 10:16 am

#209996

Hi All,
I have a stored procedure which sends an email via database mail (below) which is used by a third party application I.E: My scripting application passes the SQL code (with variables) but a limitation of this application is that it passes all the variables in UPPER case, so the email body for example IS IN UPPER CASE.
So what I want to do is have all the variables write back into a table (so as to also keep a history of all email sent) with a additional field of "EmailSent", then the stored procedure picks up the records with the EmailSent as False, from the table and once the mail is queued to update the EmailSent to True.
So What I am after is:
1) The Information to be used in the variables is written back to an EmailHistory Table, the column EmailSent value set to False..
2) A stored procedure continually checks the table for emails not yet sent - Where EmailSent value is False. (checks every minute) and then passes these variables in the record into the below stored procedure.
3) Email is queued and EmailSent column is updated to True
Current Stored Procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[udsp_VoiceSQLMail]
-- Add the parameters for the stored procedure here
@EmailTo nvarchar(MAX),
@EmailCC nvarchar(MAX),
@EmailBC nvarchar(MAX),
@EmailSubject nvarchar(MAX),
@EmailImportance nvarchar(10),
@EmailAttachments nvarchar(MAX),
@EmailBody nvarchar(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = @EmailTo,
@copy_recipients = @EmailCC,
@blind_copy_recipients = @EmailBC,
@subject = @EmailSubject,
@importance = @EmailImportance,
@file_attachments = @EmailAttachments,
@body = @EmailBody;
END
Please help... 😀

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

You have pretty much defined your problem correctly, so all you have to do is implement the changes you mention. What problems have you encountered implementing this, and what code have you created already? If you don't know where to start, then begin by looking up CREATE TABLE in the Books Online and defining what your table is going to be. From there you already have a model stored procedure for sending the emails out. You will need to do an INSERT INTO the table you create in one procedure. Then you create a procedure to be called by a SQLAGENT JOB that will send the emails out either in a CURSOR or a WHILE loop over the unsent items in the table.

One thing though is that if the variables are being passed by the application in all UPPERCASE, then you are going to be storing them all UPPERCASE in the database table.