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屋！