April 11, 2012 at 1:48 am
Hi All
Is it possible to parse multiple Parameters in the @subject or @body parameters of sp_db_sendmail
I have a Stored Proc that Ultimately sends an Email when certain criteria are met.
Basically, this is what I have
create procedure testproc
@parameter1 varchar (20),
@parameter2 int
@parameter3 int = '100'
@parameter4 int = '200'
as
if exists
/*etc etc......*/
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_Name'
@recipients = 'Random_Recipients'
@subject = @parameter3
@body = @parameter3+paramater4
It says incorrect syntax near '+'
Is it not possible to parse multiple parameters as the subject or body?
Thanks
April 11, 2012 at 1:52 am
Hi,
You can not pass parametes like this in the stored procedure, just concatenate the parameters in the local variable then assign it the subject or body.
Regards
Ashok
April 11, 2012 at 2:44 am
ashok.faridabad1984 (4/11/2012)
Hi,You can not pass parametes like this in the stored procedure, just concatenate the parameters in the local variable then assign it the subject or body.
Regards
Ashok
Like this:
declare @parameter1 varchar (20)
declare @parameter2 nvarchar(20) = @@servername
declare @parameter3 varchar(100) = 'Server: '
declare @parameter4 varchar(100) = @parameter3+@parameter2
exec msdb.dbo.sp_send_dbmail
@profile_name = 'profile_name',
@recipients = 'recipients',
@subject = @parameter3,
@body = @parameter4
Seems to Work
Thanks
April 11, 2012 at 3:11 am
derekr 43208 (4/11/2012)
ashok.faridabad1984 (4/11/2012)
Hi,You can not pass parametes like this in the stored procedure, just concatenate the parameters in the local variable then assign it the subject or body.
Regards
Ashok
Like this:
declare @parameter1 varchar (20)
declare @parameter2 nvarchar(20) = @@servername
declare @parameter3 varchar(100) = 'Server: '
declare @parameter4 varchar(100) = @parameter3+@parameter2
exec msdb.dbo.sp_send_dbmail
@profile_name = 'profile_name',
@recipients = 'recipients',
@subject = @parameter3,
@body = @parameter4
Seems to Work
Thanks
create procedure testproc
(
@parameter1 varchar (20),
@parameter2 int
@parameter3 int = '100'
@parameter4 int = '200'
)
as
begin
declare @lparameter5 int /*can be varchar depending upon your requirement whether you want to add the values or just concatenate */
set @lparameter5 = @parameter3+paramater4
if exists /*etc etc......*/
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_Name'
@recipients = 'Random_Recipients'
@subject = @parameter3
@body = @lparameter5
end
April 11, 2012 at 3:14 am
ashok.faridabad1984 (4/11/2012)
derekr 43208 (4/11/2012)
ashok.faridabad1984 (4/11/2012)
Hi,You can not pass parametes like this in the stored procedure, just concatenate the parameters in the local variable then assign it the subject or body.
Regards
Ashok
Like this:
declare @parameter1 varchar (20)
declare @parameter2 nvarchar(20) = @@servername
declare @parameter3 varchar(100) = 'Server: '
declare @parameter4 varchar(100) = @parameter3+@parameter2
exec msdb.dbo.sp_send_dbmail
@profile_name = 'profile_name',
@recipients = 'recipients',
@subject = @parameter3,
@body = @parameter4
Seems to Work
Thanks
create procedure testproc
(
@parameter1 varchar (20),
@parameter2 int
@parameter3 int = '100'
@parameter4 int = '200'
)
as
begin
declare @lparameter5 int /*can be varchar depending upon your requirement whether you want to add the values or just concatenate */
set @lparameter5 = @parameter3+paramater4
if exists /*etc etc......*/
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_Name'
@recipients = 'Random_Recipients'
@subject = @parameter3
@body = @lparameter5
end
Thanks
Got it Working
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply