SQL 执行存储过程之 [sp_executesql] - 重用SQL - 滔Roy

SQL 执行存储过程之 [sp_executesql] - 重用SQL

SQL 执行存储过程之 [sp_executesql] - 重用SQL

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

sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]

参数：

[@stmt=] statement
- 是包含Transact-SQL语句或批处理的Unicode字符串@stmt必须是Unicode常量或Unicode变量。不允许使用更复杂的Unicode表达式，例如使用+运算符连接两个字符串。不允许使用字符常量。如果指定了Unicode常量，则它必须以N作为前缀。例如，Unicode常量N'sp_who'有效，但字符常量'sp_who'无效。字符串的大小仅受可用数据库服务器内存的限制。在64位服务器上，字符串的大小限制为2 GB，即nvarchar的最大大小（max）。
- @stmt中包含的每个参数在@params参数定义列表和参数值列表中都必须有相应的条目。
[@params=]N'@parameter_name data_type[，…N]'
- 是一个字符串，包含@stmt中嵌入的所有参数的定义。字符串必须是Unicode常量或Unicode变量。每个参数定义由一个参数名和一个数据类型组成。n是表示其他参数定义的占位符。@stmt中指定的每个参数都必须在@params中定义。如果@stmt中的Transact-SQL语句或批处理不包含参数，则不需要@params。此参数的默认值为NULL。
[@param1=]'value1'
- 是参数字符串中定义的第一个参数的值。该值可以是Unicode常量或Unicode变量。必须为@stmt中包含的每个参数提供一个参数值。当@stmt中的Transact-SQL语句或批处理没有参数时，这些值不是必需的。
[ OUT | OUTPUT ]
- 指示该参数是输出参数。text、ntext和image参数可以用作输出参数，除非该过程是公共语言运行时（CLR）过程。使用output关键字的输出参数可以是光标占位符，除非该过程是CLR过程。
n
- 是其他参数值的占位符。值只能是常量或变量。值不能是更复杂的表达式，如函数或使用运算符生成的表达式。

返回值：0成功，非0 失败

其他注意事项：

sp_executesql参数必须按照本主题前面“语法”部分所述的特定顺序输入。如果输入的参数顺序不正确，将出现错误消息。
sp_executesql在批处理、名称范围和数据库上下文方面的行为与EXECUTE相同。在执行sp_executesql语句之前，不会编译sp_executesql@stmt参数中的Transact-SQL语句或批处理。然后将@stmt的内容编译并作为一个执行计划执行，该执行计划与名为sp_executesql的批处理的执行计划分开。sp_executesql批处理无法引用在调用sp_executesql的批处理中声明的变量。sp_executesql批处理中的本地游标或变量对调用sp_executesql的批处理不可见。数据库上下文中的更改只持续到sp_executesql语句的末尾。
当对Transact-SQL语句的参数值的更改是唯一的变化时，可以使用sp_executesql代替存储过程多次执行该语句。由于Transact-SQL语句本身保持不变，并且只有参数值会更改，因此SQL Server查询优化器可能会重用它为第一次执行生成的执行计划。

示例1：

use TaoRoy2022;  
go  
declare @sSQL nvarchar(500);  
declare @Parm1 nvarchar(500);  
declare @SaNum nvarchar(25);  
declare @InVar int;  
set @sSQL = N'select @SaOUT = max(SaNum)  
    from Sales.SaHer  
    where CuID = @CuID';  
set @Parm1 = N'@CuID int,@SaOUT nvarchar(25) output';  
set @InVar = 5201314;  
execute sp_executesql  
     @sSQL 
    ,@Parm1 
    ,@CuID = @InVar  
    ,@SaOUT = @SaNum output;  
select @SaNum;    -- 此select语句返回输出参数的值。
-- 此select语句使用where子句中输出参数的值。  
select sDate,sTotal from Sales.SaHer where SaNum=@SaNum;

示例2：

create procedure [dbo].[sp_GetUId]
(
    @uId varchar(100),
    @uName varchar(100) output
)
as
begin
    declare @sql nvarchar(1000)
    set @sql=N'select @uName=UName from TB1 where UId=@uId'
    exec sp_executesql @sql,N'@uId varchar(100),@uName varchar(100) output',@uId,@uName output
    select @uName
end