SQL Server转贴自微软MSDN:建议实行动态SQL时,使用sp_executesql 存储过程要并非使用EXECUTE 语句

http://msdn.microsoft.com/zh-cn/library/ms175170.aspx

 

提议你在履字符串时,使用 sp_executesql 存储过程要并非动 EXECUTE
语句子。由于此存储过程支持参数替换,因此 sp_executesql 比 EXECUTE
的成效再多;由于 SQL Server 更可能用 sp_executesql
生成的执行计划,因此 sp_executesql 比 EXECUTE 更有效。

SQL Server 1 于包含批处理)


sp_executesql 或 EXECUTE
语句执行字符串时,字符串将作为其的自包含批处理实施。SQL Server
会将字符串中之一个或多单 Transact-SQL 语句编译为单身于批判处理(包含
sp_executesql 或 EXECUTE
语句)执行计划的实践计划。下列规则适用于从包含批处理:

  • 在执行 sp_executesql 或 EXECUTE 语句之前,不见面用
    sp_executesql 或 EXECUTE 字符串中之 Transact-SQL
    语句编译到实施计划遭到。执行字符串之前,不见面分析或检查该左。执行时才对字符串中引用的称呼进行剖析。

  • 现已实施之字符串中的 Transact-SQL 语句不克看包含 sp_executesql
    或 EXECUTE 语句之批处理着宣称的另变量。包含 sp_executesql
    EXECUTE 语句之批处理不可知顾已实行字符串中定义之变量或局部游标。

  • 如果都实施字符串包含一个移数据库上下文的 USE
    语词,则针对数据库上下文所举行的改观将只是持续至 sp_executesql
    EXECUTE 语词运行了。

运行下列两单批处理说明了这些地方:

复制)

/*Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3);
SET @CharVariable = 'abc';
/* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';
GO

/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks2008R2;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

SQL Server 2 轮换参数值)


sp_executesql 支持替换 Transact-SQL 字符串中指定的另参数值,但
EXECUTE 语词不支持。因此,由 sp_executesql 生成的 Transact-SQL
字符串比那些由 EXECUTE 语句生成的字符串更加相似。SQL Server
查询优化器可能以 sp_executesql 的 Transact-SQL
语句与以前所行之口舌的尽计划相匹配,从而省去编译新的实行计划的付出。

运用 EXECUTE 语句,所有参数值都不能不变换为字符或 Unicode,并化作
Transact-SQL 字符串的等同组成部分。

假定又执行语句,则就是只有提供的参数值不同,每次执行时为务必变更全新的
Transact-SQL 字符串。这样便会以下列方式生成额外的支付:

  • 绵绵重复改字符串文本(特别是扑朔迷离 Transact-SQL 语句)中之参数值,会潜移默化
    SQL Server 查询优化器将新的 Transact-SQL
    字符串与现有执行计划相匹配的作用。

  • 老是执行时均要另行转整个字符串。

  • 历次执行时必将参数值(非字符或 Unicode 值)转换为字符或 Unicode
    格式。

sp_executesql 可以独立采取 Transact-SQL 字符串来设置参数值:

复制)

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */
SET @SQLString =
     N'SELECT * FROM AdventureWorks2008R2.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;

sp_executesql 还别起以下优点:

  • 为 Transact-SQL
    语句的实际上文本以片浅实施中不改变,所以查询优化器能够将第二赖实行中之
    Transact-SQL 语句与第一不好施行时变的执行计划相配合。因此,SQL
    Server 不必编译第二长条语句。

  • Transact-SQL 字符串只可怜成一蹩脚。

  • 整型参数按那自我格式指定。不需要换为 Unicode。

    注意

    语句字符串中的对象名称必须完全符合 SQL Server 重用执行计划的要求。

SQL Server 3 录取执行计划)


以 SQL Server 早期版本被,唯一可以引用执行计划之方法是拿 Transact-SQL
语句定义也存储过程并吃应用程序执行该存储过程。这会生出管理应用程序的额外开销。使用
sp_executesql 可推进削减是开,并允许 SQL Server
仍用执行计划。当提供给 Transact-SQL
语句的参数值只来一个变量时,如果一旦数行 Transact-SQL 语句,则可以使用
sp_executesql 而毫不使外存储过程。因为 Transact-SQL
语句我保持无转移,仅参数值发生变更,所以 SQL Server
查询优化器可能会见用第一糟施行时别的行计划。

下面的演示为服务器上的每个数据库(四只体系数据库除外)生成并履行 DBCC CHECKDB 语词。

复制)

USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases;

DECLARE @DBNameVar NVARCHAR(128);
DECLARE @Statement NVARCHAR(300);

FETCH NEXT FROM AllDatabases INTO @DBNameVar;
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'CHECKING DATABASE ' + @DBNameVar;
   SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
      + N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY';
   EXEC sp_executesql @Statement;
   PRINT CHAR(13) + CHAR(13);
   FETCH NEXT FROM AllDatabases INTO @DBNameVar;
END;

CLOSE AllDatabases;
DEALLOCATE AllDatabases;
GO
SET NOCOUNT OFF;
GO

假定尽的 Transact-SQL 语句包含绑定的参数标记时,SQL Server ODBC
驱动程序使用 sp_executesql 来实施 SQLExecDirect。这样,可以将
sp_executesql 提供的优势扩大至以 ODBC 或透过 ODBC 定义之
API(例如 RDO)的装有应用程序。连接至 SQL Server 的幸存 ODBC
应用程序无需还写就得自动获得属性上的改进。但出一个例外,如果是实施时数参数,则非可知应用
sp_executesql。有关详细信息,请参考运语句参数。

SQL Server Native Client ODBC 访问接口也采取 sp_executesql
来直接实施带有绑定参数的语句。使用 OLE DB 或 ADO
的应用程序无需重新写就可以运用 sp_executesql 具有的优点。

SQL Server 4 请参阅)


参考

DECLARE @local_variable
(Transact-SQL)

SELECT
(Transact-SQL)

sp_executesql
(Transact-SQL)

概念

SQL 注入

相关文章