How to concatenate string when setting a parameter in Transact-SQL

Question

First question here and is the following. I wrote the following code and everything works fine:

DECLARE @subject NVARCHAR(100) SET @subject = 'Report executed on ' + CONVERT(VARCHAR(12), GETDATE(), 107) SELECT @subject 

Result: Report executed on Aug 17, 2012

but when trying to concatenate the previous string while setting the parameter of msdb.dbo.sp_send_dbmail procedure, it fails

EXEC msdb.dbo.sp_send_dbmail @profile_name='XXX', @recipients='[email protected]', @subject = 'Report executed on ' + CONVERT(VARCHAR(12), GETDATE(), 107), @body= @tableHTML, @body_format = 'HTML'; 

I know I could declare and send a variable to the parameter but I would like to understand why it fails when concatenating directly in the parameter. thank you for your time and knowledge

Answer 1

Parameter values to T-SQL stored procedures cannot be expressions. They need to be either a constant or a variable.

From MSDN - Specify Parameters:

The parameter values supplied with a procedure call must be constants or a variable; a function name cannot be used as a parameter value. Variables can be user-defined or system variables such as @@spid.

Answer 2

You need to do:

DECLARE @Sub nvarchar(100); SET @Sub = 'Report executed on ' + CONVERT(VARCHAR(12), GETDATE(), 107); EXEC msdb.dbo.sp_send_dbmail @profile_name='XXX', @recipients='[email protected]', @subject = @Sub, @body= @tableHTML, @body_format = 'HTML';