Incorrect syntax near '+' in stored procedure sql server

Question

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.

Answer 1

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 '+'.