sp_executesql介绍和使用

 建议使用 sp_executesql 而不要使用 EXECUTE 语句执行字符串。支持参数替换不仅使 sp_executesql 比 EXECUTE 更通用，而且还使 sp_executesql 更有效，因为它生成的执行计划更有可能被 SQL Server 重新使用。

 

执行可以多次重用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。

语法
sp_executesql [@stmt =] stmt
[
     {, [@params =] N'@parameter_name   data_type [,...n]' }
     {, [@param1 =] 'value1' [,...n] }
]

参数
[@stmt =] stmt

包含 Transact-SQL 语句或批处理的 Unicode 字符串，stmt 必须是可以隐式转换为 ntext 的 Unicode 常量或变量。不允许使用更复杂的 Unicode 表达式（例如使用 + 运算符串联两个字符串）。不允许使用字符常量。如果指定常量，则必须使用 N 作为前缀。例如，Unicode 常量 N'sp_who' 是有效的，但是字符常量 'sp_who' 则无效。字符串的大小仅受可用数据库服务器内存限制。

stmt 可以包含与变量名形式相同的参数，例如：

N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'

stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项。

[@params =] N'@parameter_name   data_type [,...n]'

字符串，其中包含已嵌入到 stmt 中的所有参数的定义。该字符串必须是可以隐式转换为 ntext 的 Unicode 常量或变量。每个参数定义均由参数名和数据类型组成。n 是表明附加参数定义的占位符。stmt 中指定的每个参数都必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数，则不需要 @params。该参数的默认值为 NULL。

[@param1 =] 'value1'

参数字符串中定义的第一个参数的值。该值可以是常量或变量。必须为 stmt 中包含的每个参数提供参数值。如果 stmt 中包含的 Transact-SQL 语句或批处理没有参数，则不需要值。

n

附加参数的值的占位符。这些值只能是常量或变量，而不能是更复杂的表达式，例如函数或使用运算符生成的表达式。

返回代码值
0（成功）或 1（失败）

结果集
从生成 SQL 字符串的所有 SQL 语句返回结果集。

注释
在批处理、名称作用域和数据库上下文方面，sp_executesql 与 EXECUTE 的行为相同。sp_executesql stmt 参数中的 Transact-SQL 语句或批处理在执行 sp_executesql 语句时才编译。然后编译 stmt 中的内容并作为执行计划运行（独立于名为 sp_executesql 的批处理的执行计划）。sp_executesql 批处理不能引用调用 sp_executesql 的批处理中声明的变量。sp_executesql 批处理中的本地游标和变量对调用 sp_executesql 的批处理是不可见的。对数据库上下文所作的更改只在 sp_executesql 语句结束前有效。

如果只更改了语句中的参数值，则 sp_executesql 可用来代替存储过程多次执行 Transact-SQL 语句。因为 Transact-SQL 语句本身保持不变仅参数值变化，所以 Microsoft® SQL Server™ 查询优化器可能重复使用首次执行时所生成的执行计划。

 

说明   如果语句字符串中的对象名不是全限定名，则该执行计划不会被重用。

sp_executesql 支持与 Transact-SQL 字符串相独立的参数值的设置：

DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/
SET @SQLString =
      N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                       @level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                       @level = @IntVariable

替换 sp_executesql 中的参数的能力，与使用 EXECUTE 语句执行字符串相比，有下列优点：

因为在 sp_executesql 中，Transact-SQL 语句的实际文本在两次执行之间未改变，所以查询优化器应该能将第二次执行中的 Transact-SQL 语句与第一次执行时生成的执行计划匹配。这样，SQL Server 不必编译第二条语句。

Transact-SQL 字符串只生成一次。

整型参数按其本身格式指定。不需要转换为 Unicode。
权限
执行权限默认授予 public 角色。

示例
A. 执行简单的 SELECT 语句
下面的示例创建并执行一个简单的 SELECT 语句，其中包含名为 @level 的嵌入参数。

execute sp_executesql
           N'select * from pubs.dbo.employee where job_lvl = @level',
          N'@level tinyint',
           @level = 35

B. 执行动态生成的字符串
下面的示例显示使用 sp_executesql 执行动态生成的字符串。该示例中的存储过程用来向一组表中插入数据，该表用于划分一年的销售数据。一年中的每个月均有一个表，格式如下：

CREATE TABLE May1998Sales
     (OrderID       INT       PRIMARY KEY,
     CustomerID       INT       NOT NULL,
     OrderDate       DATETIME    NULL
         CHECK (DATEPART(yy, OrderDate) = 1998),
     OrderMonth       INT
         CHECK (OrderMonth = 5),
     DeliveryDate    DATETIME    NULL,
         CHECK (DATEPART(mm, OrderDate) = OrderMonth)
     )

有关从这些分区表中检索数据的更多信息，请参见使用包含分区数据的视图。

每个表的名称由月份名的前三个字母、年度的四位数字和常量 Sales 组成。名称可以从订单日期动态生成：

/* Get the first three characters of the month name. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
/* Concatenate the four-digit year; cast as character. */
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
/* Concatenate the constant 'Sales'. */
'Sales'

下面示例中的存储过程动态生成并执行一个 INSERT 语句，向适当的表中插入新订单。该存储过程使用订单日期生成应包含数据的表的名称，然后将名称并入 INSERT 语句。（这是 sp_executesql 的一个简单示例。不包含错误检查，也不包括业务规则检查，例如确保两个表之间订单号没有重复。）

CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
                  @PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT

-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
        /* Build the name of the table. */
        SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
        CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
        'Sales' +
        /* Build a VALUES clause. */
        ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
        ' @InsOrdMonth, @InsDelDate)'

/* Set the value to use for the order month because
    functions are not allowed in the sp_executesql parameter
    list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)

EXEC sp_executesql @InsertString,
     N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
        @InsOrdMonth INT, @InsDelDate DATETIME',
      @PrmOrderID, @PrmCustomerID, @PrmOrderDate,
      @OrderMonth, @PrmDeliveryDate

GO

在该过程中使用 sp_executesql 比使用 EXECUTE 执行字符串更有效。使用 sp_executesql 时，只生成 12 个版本的 INSERT 字符串，每个月的表 1 个。使用 EXECUTE 时，因为参数值不同，每个 INSERT 字符串均是唯一的。尽管两种方法生成的批处理数相同，但因为 sp_executesql 生成的 INSERT 字符串相似，所以查询优化程序更有可能反复使用执行计划。

 

请参见

批处理

EXECUTE

运行时生成语句

系统存储过程

-----------------------------------------------

动态sql语句基本语法
1 :普通SQL语句可以用Exec执行

eg:    Select * from tableName
          Exec('select * from tableName')
          Exec sp_executesql N'select * from tableName'     -- 请注意字符串前一定要加N

2:字段名，表名，数据库名之类作为变量时，必须用动态SQL

eg:   
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName               -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName')      -- 请注意 加号前后的 单引号的边上加空格

当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名

declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s)                 -- 成功
exec sp_executesql @s    -- 此句会报错

 

declare @s Nvarchar(1000)   -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s)                 -- 成功     
exec sp_executesql @s    -- 此句正确

3. 输出参数
declare @num int,
         @sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中？

declare @num int,
                @sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num

----------------------------------------

比如常用的行列转换

create table test
(
id int,
colname varchar(10),
value varchar(10)
)
insert test
select 1,        'A',         'A1' union all
select 1,        'B',         'B1' union all
select 1,        'C',         'C1' union all
select 2,        'A',         'A2' union all
select 2,        'B',         'B2' union all    
select 2,        'C',         'C2' union all
select 3,        'A',         'A3' union all
select 3,        'B',        'B3' union all
select 3,        'C',         'C3'
select * from test
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+'max(case colname when '''+colname+''' then value end) '''+colname+''''
from test group by colname
set @sql='select '+stuff(@sql,1,1,'')+' from test group by id'
print @sql
exec(@sql)