如何在Transact-SQL中设置参数时连接字符串

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

结果：报告于2012年8月17日执行

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

但是在设置msdb.dbo.sp_send_dbmail过程的参数时尝试连接上一个字符串时，它会失败

EXEC msdb.dbo.sp_send_dbmail @profile_name='XXX',
@recipients='XXXX@XXXXX.com',
@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

我知道我可以声明并向参数发送一个变量，但我想了解为什么它在参数中直接连接时失败了。谢谢你的时间和知识

2 个解决方案

#1

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

T-SQL存储过程的参数值不能是表达式。它们需要是常数或变量。

From MSDN - Specify Parameters:

从MSDN - 指定参数：

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.

过程调用提供的参数值必须是常量或变量;函数名称不能用作参数值。变量可以是用户定义的，也可以是系统变量，例如@@ spid。

#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='XXXX@XXXXX.com',
@subject = @Sub,
@body= @tableHTML,
@body_format = 'HTML';

#1