July 25, 2011 at 9:48 am
Is there a way that I can create a dynamic body using sp_send_dbmail?
For ex. (this is what is stored in the table)
Recently we invited you to participate in a survey to help + @vFirstName + become an even more effective leader.
How can I put a variable that is created in a stored procedure into a value that I get from a column in a table?
The above value is stored in a table (surveymessage varchar(1000))
when I set the variable in the stored procedure, it shows the variable literally, instead of with the variable value = 'Sam'.
@vBody = @vSurveyMessage
Is this possible??
Thank you. 🙂
July 25, 2011 at 10:38 am
You have to turn the whole statement into a dynamic SQL statement. You can't just assign variables to the various bits of it. Do you know how to do dynamic SQL or do you need an example?
July 25, 2011 at 11:17 am
Yes, please send example of how I can do this.
Thank you.
July 25, 2011 at 11:53 am
Dynamic SQL is when you populate a variable with a SQL query or another stored procedure. Read up on sp_executesql when you get a chance, so you understand what can happen with dynamic SQL.
Here's my brief example of db_send_mail. You can build out any of the parameters as external variables to be loaded at run time, or hard code them (look at the email addresses in the example). Hard coding is not recommended for using dynamic SQL, BTW.
Declare @MyEmail varchar(8000), @ProfileName varchar(25) = 'MyProfileName';
Select @MyEmail = 'sp_send_dbmail @profile_name='''
+ @ProfileName + ''', @recipients = ''myemail@emailaddy.com;youremail@emailladdy.com'',
@subject=''Hello World'', @body='
+ MyColumn from MyTable where xxx=yyyy;
Select @MyEmail; -- Troubleshooting code to make sure all the quotes are correct.
Execute sp_executesql (@MyEmail);
Or, if you want to figure out message separately.
Declare @MyEmail varchar(8000), @ProfileName varchar(25) = 'MyProfileName',
@Message varchar(300);
Select @Message = MyColumn from MyTable where xxx=yyyy
Select @MyEmail = 'sp_send_dbmail @profile_name='''
+ @ProfileName + ''', @recipients = ''myemail@emailaddy.com;youremail@emailladdy.com'',
@subject=''Hello World'', @body='
+ @Message;
Select @MyEmail; -- Troubleshooting code to make sure all the quotes are correct.
Execute sp_executesql (@MyEmail);
July 25, 2011 at 12:43 pm
Please provide the code you are using to call your sp_send_dbmail proc?
As Brandie explained, dynamic sql is one method to build a dynamic body. I typically do not go to the lengths she has done. You can just initialize a variable and then call the sp_send_dbmail proc with @body = @yournewvariable that you built.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 25, 2011 at 1:06 pm
Thank you. Got it.
But, that is not what I want to do.
I want to retrieve a local variable that is stored in aolumn in a table. For ex. in the column survey.surveymessage = 'Recently we invited you to participate in a survey to help ' + [@vfirstname]'. How can I put this value into the body of the email? I want the variable to include the name of the person, not the literal. Is this possible.
I tried:
DECLARE@vFirstNamevarchar(25)
Declare@vBodyvarchar(1000)
DECLARE@vSurveyMessagevarchar(1000)
SELECT @vfirstname = firstname, @vSurveyMessage=SurveyMessage
from Survey
SET @vBody = @vSurveyMessage
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SLI_email', -- Use mail profile just for SLI
@recipients = dbdiva@email.orgl,
@body = @vBody,
@subject = 'Survey';
July 25, 2011 at 1:12 pm
Can you provide sample data from this table along with table def so we can test and provide a more accurate solution?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 25, 2011 at 1:15 pm
I hope this helps.
SELECT TOP 1000 [SurveyID]
,[SurveyTypeID]
,[Survey]
,[SurveyDesc]
,[StartDate]
,[EndDate]
,[SurveyURL]
,[SurveyMessage]
,[SurveyStatus]
,[ModifyDate]
,[CreateDate]
,[AppUserID]
FROM [ServantLeader_TEST].[dbo].[Survey]
SurveyIDSurveyTypeIDSurveySurveyDescStartDateEndDateSurveyURLSurveyMessageSurveyStatusModifyDateCreateDateAppUserID
23LSI TrainingNULL2011-07-112011-08-07http://www.lsitraining.com'Recently we invited you to participate in a survey to help ' + [@vfirstname]A2011-07-18 13:20:24.0002011-07-18 13:20:24.0001
July 25, 2011 at 1:16 pm
if @vSurveyMessage has the literal value of thiis:
@vSurveyMessage = 'Recently we invited you to participate in a survey to help ' + [@vfirstname]'
then you'll need to do a REPLACE of the string:
something like this:
SELECT @vfirstname = firstname, @vSurveyMessage= REPLACE(SurveyMessage,'[@vfirstname]',firstname)
from Survey
Lowell
July 25, 2011 at 1:22 pm
Lowell (7/25/2011)
if @vSurveyMessage has the literal value of thiis:
@vSurveyMessage = 'Recently we invited you to participate in a survey to help ' + [@vfirstname]'
then you'll need to do a REPLACE of the string:
something like this:
SELECT @vfirstname = firstname, @vSurveyMessage= REPLACE(SurveyMessage,'[@vfirstname]',firstname)
from Survey
That's all that can be done with this
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 25, 2011 at 1:34 pm
Thanks. It is working now. Very Clever. 🙂
What if I needed to replace more then one field, ie. firstname and lastname?
Can you help me with the syntax?
July 25, 2011 at 1:36 pm
The way I've done this before is use tags in the body to indicate the parts that are going to be built, and then use replace statements and select statements to build it.
I've even gone so far as to dynamically choose which paragraphs to include.
If exists (select 1 from dbo.Orders where Acct = @Acct and ItemID in (... business rule here ...)
set @Body = @Body + replace((select BodySnippet from dbo.EmailParagraphs where ID = 3), '[CustName]', @FName);
That kind of thing. Preset tags that can be replaced, marked with brackets around them in the text.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2011 at 1:38 pm
DB Diva (7/25/2011)
Thanks. It is working now. Very Clever. 🙂What if I needed to replace more then one field, ie. firstname and lastname?
Can you help me with the syntax?
Same sort of thing
SELECT @vfirstname = firstname, @vSurveyMessage= REPLACE(REPLACE(SurveyMessage,'[@vlastname]',lastname),'[@vfirstname]',firstname)
from Survey
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 26, 2011 at 9:21 am
Thank you. Works great now.
Thanks for the quick response too. 🙂
July 26, 2011 at 9:26 am
You are welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply