在存储过程参数列表中使用表达式的结果（例如函数调用）？ [英] Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
问题描述
我想写一个存储过程来协助开发我们的数据库，但我有一些麻烦使用它。例如：
I'm trying to write a stored procedure to assist with development of our database, but I'm having some trouble using it. For example:
DECLARE @pID int;
SET @pID = 1;
EXEC WriteLog 'Component', 'Source', 'Could not find given id: ' + CAST(@pID AS varchar);
这会产生错误（在SQL Server 2005上）
This yields the error (on SQL Server 2005)
Msg 102，Level 15，State 1，Line 4
'+'附近的语法不正确。
Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '+'.
有人可以向我解释为什么我的语法不正确，以及解决这个问题的正确方法吗？
Can someone explain to me why my syntax is incorrect, and the right way to solve this problem?
推荐答案
您需要使用中间变量。 SQL Server不支持参数列表中的这种操作，虽然它已经在TODO列表上了几年！ （请参见 Connect项目：使用标量函数作为存储过程参数）
You need to use an intermediate variable. SQL Server does not support this kind of operation in the parameter list itself though it has been on the TODO list for a few years! (See Connect Item: Use scalar functions as stored procedure parameters)
EXEC 语法
[ { EXEC | EXECUTE } ]
{
[ @return_status = ]
{ module_name [ ;number ] | @module_name_var }
[ [ @parameter = ] { value
| @variable [ OUTPUT ]
| [ DEFAULT ]
}
]
[ ,...n ]
[ WITH <execute_option> [ ,...n ] ]
}
[;]
文档目前对于 value 的可接受格式并不清楚，但似乎只是简单表达式，如文字值或 @@ 前缀系统函数（例如 @@ IDENTITY ）。不允许使用其他系统函数，例如 SCOPE_IDENTITY（）（即使那些不需要括号的 CURRENT_TIMESTAMP
The documentation is not currently that clear on an acceptable format for value but it seems to be only "simple" expressions such as literal values or @@ prefixed system functions (such as @@IDENTITY). Other system functions such as SCOPE_IDENTITY() are not permitted (even those which do not require parentheses such as CURRENT_TIMESTAMP are not allowed).
So for the time being you need to use syntax such as the below所以暂时需要使用下面的语法
DECLARE @pID INT;
SET @pID = 1;
/*If 2008+ for previous versions this needs to be two separate statements*/
DECLARE @string VARCHAR(50) = 'Could not find given id: ' + CAST(@pID AS VARCHAR(11))
EXEC WriteLog
'Component',
'Source',
@string
这篇关于在存储过程参数列表中使用表达式的结果（例如函数调用）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
