T-SQL:无法将串联字符串作为参数传递给存储过程 [英] T-SQL: Cannot pass concatenated string as argument to stored procedure
问题描述
场景:需要将 n 个参数传递给存储过程.其中一个参数是 varchar(x)
类型.该 varchar 参数需要由少数其他 varchar 变量构造而成.此问题使用 SQL Server 2005,但此行为适用于所有版本的 SQL Server.
Scenario:
Need to pass n arguments to a stored procedure. One of the arguments is of type varchar(x)
. That varchar argument needs to be constructed from a handful of other varchar variables. This problem uses SQL Server 2005, but this behaviour applies to all versions of SQL Server.
设置:
DECLARE @MyString varchar(500), @MyBar varchar(10), @MyFoo varchar(10)
SELECT @MyBar= 'baz '
SELECT @MyFoo= 'bat '
-- try calling this stored procedure!
EXEC DoSomeWork @MsgID, 'Hello ' + @MyBar + '" world! "' + @MyFoo + '".'
这会在 SQL Server 中产生异常:'+' 附近的语法不正确
.通常,您可能认为数据类型是错误的(即变量的类型不同,但会产生不同的错误消息).
This produces the exception in SQL Server: Incorrect syntax near '+'
. Typically you might think that the datatype would be wrong (i.e. the variables are of different types, but that would produce a different error message).
这是一个正确的实现,编译没有错误:
Here's a correct implementation that compiles without error:
SELECT @MyString= 'Hello ' + @MyBar + '" world! "' + @MyFoo + '".';
EXEC DoSomeWork @ID, @MyString
问题:为什么 T-SQL 无法将 varchar 的串联作为参数处理?它知道类型,因为它们被正确地声明为 varchar.
Question: Why is it that T-SQL can't handle the concatenation of a varchar as an argument? It knows the types, as they were declared properly as varchar.
推荐答案
EXECUTE 语句的语法与其他语句(如 SELECT 和 SET)不同.例如,观察以下两页顶部的语法部分.
The EXECUTE statement simply has a different grammar then other statements like SELECT and SET. For instance, observe the syntax section at the top of the following two pages.
EXECUTE 语句:http://msdn.microsoft.com/en-us/library/ms188332.aspx
EXECUTE statement: http://msdn.microsoft.com/en-us/library/ms188332.aspx
SET 声明:http://msdn.microsoft.com/en-us/library/ms189484.aspx
EXECUTE 的语法只接受一个值
The syntax for EXECUTE only accepts a value
[[@parameter =] {值 |@多变的[输出] |[默认]]
[[@parameter =] {value | @variable [OUTPUT] | [DEFAULT]]
而 SET 的语法接受一个表达式
Whereas the syntax for SET accepts an expression
{@local_variable = 表达式}
{@local_variable = expression}
一个值基本上只是一个硬编码的常量,但一个表达式将被评估.这就像拥有 varchar 'SELECT 1 + 1'.现在它只是一个 varchar 值.但是,您可以像这样评估字符串:
A value is basically just a hard coded constant, but an expression is going to be evaluated. It's like having the varchar 'SELECT 1 + 1'. It's just a varchar value right now. However, you can evaluate the string like this:
EXEC('SELECT 1 + 1')
我想我所指出的只是 EXEC 命令根据定义不允许表达式,而您显然已经发现了这一点.我不知道 T-SQL 的开发人员在这样做时的意图是什么.我想如果你允许在存储过程的参数列表中的子查询中抛出子查询,语法就会失控.
I suppose all I'm pointing out is that the EXEC command doesn't allow expressions by definition, which you apparently found out already. I don't know what the intention of the developers of T-SQL where when they made it that way. I suppose the grammar would just get out of hand if you where allowed to throw subqueries within subqueries in the parameter list of a stored procedure.
T-SQL 表达式:http://msdn.microsoft.com/en-us/library/ms190286.aspx
这篇关于T-SQL:无法将串联字符串作为参数传递给存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!