转贴自微软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 更有效。

图片 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

图片 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 重用执行计划的要求。

图片 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 具有的助益。

图片 4 请参阅)


参考

DECLARE @local_variable
(Transact-SQL)

SELECT
(Transact-SQL)

sp_executesql
(Transact-SQL)

概念

SQL 注入

相关文章