3

I am writing a stored procedure which will accept a parameter and send mail to some recipients. I am using the sql builtin stored procedure.

here is the code

create proc send_mail_admin(@usage_data as varchar(200))
as
begin

--get the list of mail admins from Users

declare @admin_list varchar(max);



set @admin_list = '';

--query to get all the admins

begin
    select 
        @admin_list = @admin_list + email + ';'
    from
        Users;
end

--call the mail stored procedure

exec msdb.dbo.sp_send_dbmail 
@profile_name='some_mail_profile',
@recipients=@admin_list,
@subject='Mail from sql server',
@body=@usage_data + ' end of data',
@importance='HIGH' ;



end;

I am getting an error in the + symbol. If I remove the + symbol and execute, the procedure is getting executed. Otherwise it is giving error. A little help please.

I am trying this on a knowledge based. So feel free to share the disadvantages of sending a mail like this if there are any.

Thank you.

9

The msdb.dbo.sp_send_dbmail procedure (or other procedures) only accept either a parameter or a literal value.

Consider changing the execution of sp_send_dbmail to this:

SET @usage_data += ' end of data'

exec msdb.dbo.sp_send_dbmail 
@profile_name='some_mail_profile',
@recipients=@admin_list,
@subject='Mail from sql server',
@body=@usage_data,
@importance='HIGH' ;

An example with a user defined stored procedure:

CREATE PROCEDURE dbo.bla(@dbname varchar(255))
as 
SELECT * 
FROM sys.databases  
WHERE NAME = @dbname;
GO

DECLARE @dbname varchar(200);
SET @dbname ='Half';

EXEC dbo.bla @dbname+'OtherHalf';

Msg 102, Level 15, State 1, Line 46 Incorrect syntax near '+'.

  • thank you, that worked. So in cases like this, we cannot use the + symbol and append sme more lines to a variable, before passing to a store procedure as an argument. – Wan Street Sep 17 at 11:49
  • Is this method of sending mail proper in production environments? – Wan Street Sep 17 at 11:58
  • This is apparently pretty common in my organization. Almost every mail alert we have is built this way, with a very few exceptions where the built-in job alerts was used – John Herbert Sep 17 at 12:04
  • hmmm.. got it.. thanks a lot guys – Wan Street Sep 17 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.