动态执行SQL语句，接收返回值

一、exec和sp_executesql介绍

当需要根据外部输入的参数来决定要执行的SQL语句时，常常需要动态来构造SQL查询语句。比如，一个比较通用的分页存储过程，可能需要传入表名，字段，过滤条件，排序等参数，而对于搜索的话，可能要根据搜索条件判断来动态执行SQL语句。

　　在SQL Server中有两种方式来执行动态SQL语句，分别是exec和sp_executesql。sp_executesql相对而言具有更多的优点，它提供了输入输出接口，可以将输入输出变量直接传递到SQL语句中，而exec只能通过拼接的方式来实现。还有一个优点就是sp_executesql，能够重用执行计划，这就大大提高了执行的性能。所以一般情况下建议选择sp_executesql来执行动态SQL语句。

　　使用sp_executesql需要注意的一点就是，它后面执行的SQL语句必须是Unicode编码的字符串，所以在声明存储动态SQL语句的变量时必须声明为nvarchar类型，否则在执行的时候会报“过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@statement'”的错误，如果是使用sp_executesql直接执行SQL语句，则必须在前面加上大写字母N，以表明后面的字符串是使用Unicode类型编码的。

在SQL中，字符串前加N，表示双字节字符，即字符串用Unicode方式存储。对于西文字符，用一个字节来存储过足够了，对于东方文字字符，就需要两个字节来存储。Unicode 为了统一、规范、方便、兼容，就规定西文字符也用两个字节来存储。

下面看几种不同的动态执行SQL的例子

--1，普通SQL
exec ('select GETDATE();');
exec sp_executesql N'select GETDATE();';--加上N，不用Unicode表示方式会报错

--2，带参数的SQL语句
declare @tempsql nvarchar(1024);
declare @start_date datetime;
set @start_date = '2018-12-24';
set @tempsql = 'select DATEDIFF(day, @start_date,GETDATE());';

exec('select DATEDIFF(day, ''' + @start_date + ''',GETDATE());')
exec sp_executesql @tempsql, N'@start_date datetime',@start_date;

--3，有返回值的SQL语句
declare @tempsql nvarchar(1024);
declare @temp datetime;
set @tempsql = N'select @temp = GETDATE();';

exec sp_executesql @tempsql,N'@temp datetime output',@temp output;
select @temp

执行普通SQL时，exec和sp_executesql没有太大差别。

执行带参数的sql时，exec只能以字符串拼接的方式执行，sp_executesql可以以传参的方式执行，更安全一些

执行有返回值的sql时，只能使用sp_executesql

二、存储过程中动态执行SQL，并且获取执行的值

假如在存储过程中需要动态执行SQL，并且还要把动态执行的结果，比如行数、sum的总数等，赋值给变量，或者赋值到临时表里，那么就可以用exec和sp_executesql结合使用，如下例子

IF object_id('tempdb..#tempTB') IS NOT NULL
BEGIN
DROP TABLE #tempTB
END

--创建临时表
CREATE TABLE #tempTB(
testTime datetime NULL,
testTime2 datetime NULL,
)

--将查询的数据insert到临时表
declare @tempSQL nvarchar(1000);
set @tempSQL = 'insert into #tempTB (testTime,testTime2) select GETDATE(), DATEADD(day, 1,GETDATE());';
exec(@tempSQL);
select * from #tempTB;

--动态查询临时表行数，并赋值到变量
declare @tempCon int;
set @tempSQL = N'select @tempCon = count(*) from #tempTB;';
exec sp_executesql @tempSQL,N'@tempCon int output',@tempCon output;
select @tempCon;

DROP TABLE #tempTB