sp_executesql 带输出参数的 EXEC 动态执行 (z)

1.

http://blog.sina.com.cn/s/blog_53f01b900100ntoc.html

 

通常执行sql语句，大家用的都是exec，exec功能强大，但不支持嵌入参数，sp_executesql解决了这个问题。抄一段sqlserver帮助：

sp_executesql

执行可以多次重用或动态生成的 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 语句返回结果集。


例子（感谢邹建提供）

declare @user varchar(1000)

declare @moTable varchar(20)

select @moTable = 'MT_10'

declare @sql nvarchar(4000)  --定义变量,注意类型

set @sql='select @user = count(distinct userid)  from '+@moTable  --为变量赋值

--执行@sql中的语句

exec sp_executesql @sql

  ,N'@user varchar(1000) out'  --表示@sql中的语句包含了一个输出参数

  ,@user out                   --和调用存储过程差不多,指定输出参数值

print @user

 本例中，@moTable 为嵌入参数。

 

 

2.

http://xuelingzhi.blog.163.com/blog/static/34070034201032011730777/

/***************输入一个参数********************/
declare @sql Nvarchar(1000)
declare @tt int
declare @t1 int
set @t1=55;
--set @sql=N' insert into @ab select 2'
exec sp_executesql
   N'select @t2' --执行的语句，若是变量的话，只可以是Nvarchar变量   
   ,N'@t2 int' --声明执行语句内的参数，若是变量的话，只可以是Nvarchar变量
   ,@t2=@t1 --为语句内的参数赋值，左边是语句内的参数，右边是值。
/****************输入多个参数********************/
declare @sql Nvarchar(1000)
declare @tt int
declare @t1 int
set @t1=55;
--set @sql=N' insert into @ab select 2'
exec sp_executesql
   N'select @t2,@t1' --执行的语句，若是变量的话，只可以是Nvarchar变量
   ,N'@t2 int,@t1 int' --声明执行语句内的参数，若是变量的话，只可以是Nvarchar变量  
   ,@t2=@t1,@t1=88   --为语句内的参数赋值，左边是语句内的参数，右边是值。
/**************只输出一个参数*********************/
declare @sql Nvarchar(1000)
declare @tt2 int
--set @sql=N' insert into @ab select 2'
exec sp_executesql
   N'select @t2=200' --执行的语句，若是变量的话，只可以是Nvarchar变量
   ,N'@t2 int output' --声明执行语句内的参数，若是变量的话，只可以是Nvarchar变量  
   ,@tt2 output   --外部变量接收输出output。
select @tt2

/**********同时输入参数又输出参数************/
--declare @tab table(cc int)
declare @sql Nvarchar(1000)
declare @tt int
declare @t1 int
set @t1=50;
--set @sql=N' insert into @ab select 2'
exec sp_executesql
   N'select @tt2=@t2+@t3'   --执行的语句，若是变量的话，只可以是Nvarchar变量
   ,N'@t2 int,@t3 int,@tt2 int output' --为语句内的参数赋值，左边是语句内的参数，右边是值。
   ,@t2=@t1,@t3=50,@tt2=@tt output --语句内的参数赋值--左边是语句内的参数，右边是值。
        --输出参数--声名外部的接收变量,因为有了输入参数，因此必须指明那个语句内变量为输出变量.
select @tt

=========================================================================

exec   sp_executesql   @sqlstr,N'@re   int   out',@ret   out  

 

 

3.

http://www.cnblogs.com/edobnet/archive/2005/04/24/144509.html

SQL server 动态SQL对变量讲行赋值

执行如下SQL

declare @t int
exec('set @t=1')
print(@t)

报如下错误！
服务器: 消息 137，级别 15，状态 2，行 1
必须声明变量 '@t'。

 

 实际就是变量与动态语句不能共享，

那么换成这样就行，

declare @t int
set @t=1
print(@t)

或

exec('declare @t int;
set @t=1;
print(@t)')

但是有时候你一定要，变量与动态SQL结合起来。
比如，你在存储过程中定义一个输出参数，◎COUNT int output
而你在获得这个值的最好的方法就是动态SQL，那么有什么好的方式吗？
那就要用到系统存储过程，sp_executesql

 
         declare @sql2 nvarchar(500)
           set @sql2 = 'select @COUNT = count(distinct('+@groupby+')) from TG_ENTRY where '+@sqlWhere
    execute sp_executesql 
          @sql2,
          N'@COUNT int output',
          @TOTAL_COUNT output