execute和sp_executesql的区别

execute相信大家都用的用熟了，简写为exec，除了用来执行存储过程，一般都用来执行动态sql。而sp_executesql则是sql2005中引入的新的系统存储过程，也是用来处理动态sql的。

DECLARE @sql AS NVARCHAR(200),@count INT;
SET @sql = N'SELECT @count=max(id) from VisitLog_20211031 with(nolock)';
EXEC sp_executesql @sql, N'@count AS INT OUTPUT', @count OUTPUT;
SELECT @count;

1.区别：
它们之间最大的区别是嵌入式的参数，如下面一个语句:

declare @name varchar(10)
declare @sql nvarchar(2000)
set @name = 'master' 
set @sql = 'select count(*) from sys.databases where name = '''+@name+''''
exec (@sql)

我想把得到的count(*)传出来，用传统的exec是不好办到的，但是用sp_executesql则很容易就办到了：

declare @name varchar(10)
declare @count int
declare @sql nvarchar(2000)
set @name = 'master' 
set @sql = 'select @count = count(*) from sys.databases where name = '''+@name+''''
exec sp_executesql @sql, N'@count int out,@name varchar(20)',@count out,@name
print @count

2.性能：
如果用exec，由于每次传入的@id不一样，所以每次生成的@sql就不一样，这样每执行一次SQL Server就必须重新将要执行的动态Sql重新编译一次。但是sp_executesql则不一样，由于将数值参数化，要执行的动态Sql永远不会变化，只是传入的参数的值在变化，那每次执行的时候就不用重新编译，速度自然快多了。

注意：

• sp_executesql要求动态sql和动态sql参数列表必须是nvarchar，
• 动态Sql的参数列表与外部提供值的参数列表顺序必需一致，如： N'@count int out,@id varchar(20)',@count,@id中@count对应@count,@id对应@id 如果不一致，必须显式标明，如： N'@count int out,@id varchar(20)', @id＝@id, @count=@count
• 动态SQl的参数列表与外部提供参数的参数列表参数名可以同名.

3.参数化查询以防止SQL注入
sp_executesql 是sql server动态执行一段可以带有参数（内参，外参）的语句文本的系统存储过程，传入sp_executesql 的参数会以参数的形式传递，不会是以拼凑sql的形式传递，所以能够在不得不拼接sql语句的情景下使用，以防止sql注入。不得不拼接sql的情景包括：传递in内参数、动态决定表列、列名，还有就是like，为防止sql注入，也不得不拼接sql。

4.使用recompile强制重新编译
sp_executesql参数化查询的一个优点就是查询计划的重用，但是有时候确恰巧相反，正因为计划重用，当数据分布发生了很大变化，需要改变查询计划以适应新的数据分布时，参数化查询则仍会沿用原来的计划，而单独的sql语句或者非参数化sql则每执行一次都会重新编译创建新的查询计划。即使手动更新了统计信息，仍然是一样的查询计划，为什么呢？

因为sp_executesql本身就是一个存储过程，它执行动态语句的参数是不会被利用上的，所以当第一次编译的时候产生的计划，存储过程是无法嗅探到的，第一次编译的查询计划是怎样，第二次第三次不会改变了。

如何才能改变这一现状呢？可以使用提示符，recompile强制让存储过程在执行的时候重新编译，来获得最好的执行计划，不过这也是有代价的，就是每次都需要编译，不过相比那些被浪费掉的IO，对一些大表的性能低下的查询计划还是很值得的。例如：

exec sp_executesql N'select * from orders where customerid = @cid option(recompile) ', N'@cid as nchar(5)',@cid = @customerid;