用变量来取得exec('')的标量返回值.

  用变量来取得exec('')的标量返回值.   <script src="http://blog.csdn.net/count.aspx?ID=214734&Type=Rank" type="text/javascript"></script>   

1,问题;
 用变量来取得一段sql语句的返回时;
 可通过SET @re =(select .....)------标量值
 但有"select ....."可能包含变量等情况以至不能使用"SET @re =(select .....)"此形式;
 exec('select count(1) from
  openquery(winy,''select 1 from gzcj.test where 登记号='''''+@DjNum+''''''')')
 使用EXEC(@SQL)返回标量值;那么@SQL可以包含有变量.
 但EXEC(@SQL)形式,不能用变量来取得EXEC的返回值;

2,解决办法;
 通过sp_executesql
 {执行可以多次重用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。}----联机帮助

3,例子;

3,1
create table test(id int identity(1,1), a varchar(20))
go
insert test(a)
select 'dd'
union all select 'dee'
union all select 'dddsdds'
go

        declare @num int,
        @sqls nvarchar(4000)
        set @sqls='select @a = max(id) from test'
        exec sp_executesql @sqls,N'@a int output',@num output
        select @num
        go

drop table test
go

3,2
exec(@sql)形式:
         exec('select max(档号) from openquery(winy,''select 档号 from gzcj.test where 登记号='''''+@DjNum+''''''')')

改成sp_executesql 形式
        declare @djnum varchar(30)
        set @djnum='1005'
        declare @num varchar(30),
        @sqls nvarchar(4000)
        set @sqls ='select max(档号) from openquery(winy,''select 档号 from gzcj.test
where 登记号='''''+@DjNum+''''''')'
        exec sp_executesql @sqls,N'@a int output',@num output
-----------------------------------------------------

4,sp_executesql 联机上的例子;
           execute sp_executesql
          N'select * from pubs.dbo.employee where job_lvl = @level',
          N'@level tinyint',
          @level = 35

 

摘自http://blog.csdn.net/yionline/archive/2004/12/13/214734.aspx