T-SQL EXEC与sp_exec [英] T-SQL EXEC versus sp_exec

问题描述

我尝试了以下操作:

declare @var2 nvarchar(30)
declare @qsql nvarchar(100)

set @var2 = N'iddelegat'

exec ('select max('+ @var2 + ') as IDexec from delegat');

set @qsql = 'select max(@varsp) as IDspexec from delegat';

exec sp_executesql @qsql, N'@varsp nvarchar(30)', @var2;

结果:

IDexec        IDspexec
-----------------------    
500038       iddelegat

我不明白为什么sp_executesql不能返回与EXECUTE相同的结果.正确的返回值仅在EXECUTE语句中.似乎sp_executesql不会评估字符串'iddelegat'来返回该列.

I could not understand why sp_executesql does not return the same result as EXECUTE. The right returned values are only in EXECUTE statement. It seems that sp_executesql does not evaluate the string 'iddelegat' to return the column.

推荐答案

使用sp_executesql时无法参数化列名或表名.因此，当您在exec()中插入值iddelegat时，您将获得该列.将其用作sp_executesql的参数时，您会得到'iddelegat' －具有列名称的字符串.

You cannot parameterize column names or table names when using sp_executesql. So, when you plug in the value iddelegat in the exec(), you are getting the column. When you have it as a parameter to sp_executesql, you are getting 'iddelegat' -- a string with the name of the column.

您可以参数化的问题在SQL文档中没有得到很好的解释.如果您挖掘足够远，您将得到以下信息:

The issue of what you can parameterize is not well explained in the SQL documentation. If you dig far enough, you will get this:

可以在何处使用参数

您可以将参数用作文字值的占位符-对于任一 文字或数值.最常见的是，参数用作 搜索条件中单个行或组的占位符 (即，在SQL语句的WHERE或HAVING子句中).

You can use parameters as placeholders for literal values — for either text or numeric values. Most commonly, parameters are used as placeholders in search conditions for individual rows or for groups (that is, in the WHERE or HAVING clauses of an SQL statement).

这是文档中一个晦涩的部分，但是它具有正确的总体思路.

This is in an obscure part of the documentation, but it has the correct general idea.

为了帮助进一步理解这一点，SQL引擎可以使用参数编译查询以获取执行计划.为此，它需要知道查询中的实际列和表.可以在编译步骤之后的 中插入额外的值(参数).

To help further understand this, the SQL engine can compile queries with parameters to get the execution plan. To do this, it needs to know the actual columns and tables in the query. Extra values -- parameters -- can be plugged in after the compilation step.

这篇关于T-SQL EXEC与sp_exec的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！