在存储过程参数列表中使用表达式的结果(例如函数调用)? [英] 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屋!