sql server 获取动态sql输出结果

最新推荐文章于 2021-02-02 01:01:08 发布

转载最新推荐文章于 2021-02-02 01:01:08 发布 · 3.9k 阅读

文章标签：

#sql server

SQL SERVER 专栏收录该内容

1 篇文章

订阅专栏

本文介绍如何使用sp_executesql过程在SQL Server中动态执行SQL语句并处理输出参数，包括单个及多个输出结果的获取方法。

语法

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

用法实例
1、获取一个输出结果

使用output获取输出结果的值

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

2、两个输出结果

declare @num int, @sqls nvarchar(4000) ,@sname char(10)
set @sqls='select @a=sno,@b=sname from aa '
exec sp_executesql @sqls,N'@a int output,@b char(10) output',@num output,@sname output
select @num
select @sname

3、输入参数

DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OID INT;
SET @TableName = 'aa ';
SET @OID = 1;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE sno ='+ cast(@OID as char(10)) ;
exec sp_executesql @sql

对于输入参数也可以直接写到语句里面

DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OID INT;
SET @TableName = 'aa ';
SET @OID = 1;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE sno =@oid'
exec sp_executesql @stmt= @sql,
@params = N'@OID AS INT ',
@OID = @OID

或

DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OID INT;
SET @TableName = 'aa ';
SET @OID = 1;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE sno =@oid'
exec sp_executesql @sql, N'@OID AS INT ', @OID