exec sp_executesql @sql和exec（@sql）SQL Server [英] exec sp_executesql @sql and exec (@sql) SQL Server

问题描述

来自 lobodava 的动态SQL查询是：

A Dynamic SQL query from lobodava is:

declare @sql nvarchar(4000) =
    N';with cteColumnts (ORDINAL_POSITION, COLUMN_NAME) as 
    (
        select ORDINAL_POSITION, COLUMN_NAME 
        from INFORMATION_SCHEMA.COLUMNS 
        where TABLE_NAME = N'''+ @tableName + ''' and COLUMN_NAME like ''' + @columnLikeFilter + '''
    ),
    cteValues (ColumnName, SumValue) as
    (
        SELECT ColumnName, SumValue
        FROM 
           (SELECT ' + @sumColumns + '
           FROM dbo.' + @tableName + ') p
        UNPIVOT
           (SumValue FOR ColumnName IN 
              (' + @columns + ')
        )AS unpvt 
    )
    select row_number() over(order by ORDINAL_POSITION) as ID, ColumnName, SumValue
    from cteColumnts c inner join cteValues v on COLUMN_NAME = ColumnName
    order by ORDINAL_POSITION'

exec sp_executesql @sql

--OR

exec（@sql）

lobodava为什么选择 exec sp_executesql @sql 而不是 exec（@sql）
那么这里有什么区别？

在递归动态上使用 sp_executesql更好吗？查询？

在其他帖子中，他们说 sp_executesql 更有可能促进查询计划的重用...
这样对这类查询有帮助吗？

Why did lobodava pick exec sp_executesql @sql and not exec(@sql) So what is the difference here?
Is it better to use sp_executesql on recursive dynamic queries?
In other post they say sp_executesql is more likely to promote query plan reuse... So it helps in these kind of queries?

推荐答案

因为 EXEC sp_executesql 将缓存查询计划- EXEC 不会。有关更多信息和非常好的阅读，请参见：

Because EXEC sp_executesql will cache the query plan -- EXEC will not. For more info, and a very good read, see:

• 动态SQL的诅咒和祝福

• The Curse and Blessings of Dynamic SQL

缓存查询表示物流暂时存储到查询中，并使其稍后更快地运行查询。

Caching a query means that the logistics to the query are temporarily stored, and make running the query later on faster for it.

这篇关于exec sp_executesql @sql和exec（@sql）SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！