为什么在EXEC的参数中串联字符串有时会在T-SQL中引起语法错误？ [英] Why does concatenating strings in the argument of EXEC sometimes cause a syntax error in T-SQL?

问题描述

在MS SQL Server Management Studio 2005中，运行此代码

In MS SQL Server Management Studio 2005, running this code

EXEC('SELECT * FROM employees WHERE employeeID = ' + CAST(3 AS VARCHAR))

出现此错误：'附近的语法不正确CAST'

但是，如果我这样做，它会起作用：

However, if I do this, it works:

DECLARE @temp VARCHAR(4000)
SET @temp = 'SELECT * FROM employees WHERE employeeID = ' + CAST(3 AS VARCHAR)
EXEC(@temp)

我在这里找到了解释： T-SQL：无法将串联的字符串作为参数传递给存储过程

I found an explanation here: T-SQL: Cannot pass concatenated string as argument to stored procedure

根据接受的答案， EXEC 可以将局部变量或值作为其参数，但不能使用表达式。

According to the accepted answer, EXEC can take a local variable or a value as its argument, but not an expression.

但是，如果是这种情况，为什么这样做？

However, if that's the case, why does this work:

DECLARE @temp VARCHAR(4000)
SET @temp = CAST(3 AS VARCHAR)
EXEC('SELECT * FROM employees WHERE employeeID = ' + @temp)

'SELECT * FROM employee WHERE employeeID ='+ @temp 在我看来肯定是一个表达式，但是代码执行没有错误。 / p>

'SELECT * FROM employees WHERE employeeID = ' + @temp sure looks like an expression to me, but the code executes with no errors.

推荐答案

文档指出， EXEC 可以采用字符串变量，常量T-SQL字符串或二者的组合/串联。

The documentation states that EXEC can take either a string variable, a constant T-SQL string, or combinations/concatenations of both of them.

您的为什么要这样做示例使用一个常量T-SQL字符串和一个字符串变量的串联，因此完全合法。

Your "why does this work" example uses a concatenation of a constant T-SQL string and a string variable, and so is perfectly legal.

这篇关于为什么在EXEC的参数中串联字符串有时会在T-SQL中引起语法错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！