程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> SQL 中sp_executesql存儲進程的應用贊助

SQL 中sp_executesql存儲進程的應用贊助

編輯：MSSQL

SQL 中sp_executesql存儲進程的應用贊助。本站提示廣大學習愛好者：（SQL 中sp_executesql存儲進程的應用贊助）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL 中sp_executesql存儲進程的應用贊助正文

摘自SQL server贊助文檔
對年夜家優查詢速度有贊助!

建議應用 sp_executesql 而不要應用 EXECUTE 語句履行字符串。支撐參數調換不只使 sp_executesql 比 EXECUTE 更通用，並且還使 sp_executesql 更有用，由於它生成的履行籌劃更有能夠被 SQL Server 從新應用。

自包括批處置

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. */
sp_executesql N'PRINT @CharVariable'
GO

/* Show database context resetting after sp_executesql completes. */
USE pubs
GO
sp_executesql N'USE Northwind'
GO
/* This statement fails because the database context
  has now returned to pubs. */
SELECT * FROM Shippers
GO

調換參數值

sp_executesql 支撐對 Transact-SQL 字符串中指定的任何參數的參數值停止調換，然則 EXECUTE 語句不支撐。是以，由 sp_executesql 生成的 Transact-SQL 字符串比由 EXECUTE 語句所生成的更類似。SQL Server 查詢優化器能夠未來自 sp_executesql 的 Transact-SQL 語句與之前所履行的語句的履行籌劃相婚配，以勤儉編譯新的履行籌劃的開支。

應用 EXECUTE 語句時，必需將一切參數值轉換為字符或 Unicode 並使其成為 Transact-SQL 字符串的一部門：

DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
/* Build and execute a string with one parameter value. */
SET @IntVariable = 35
SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' +
         CAST(@IntVariable AS NVARCHAR(10))
EXEC(@SQLString)
/* Build and execute a string with a second parameter value. */
SET @IntVariable = 201
SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' +
         CAST(@IntVariable AS NVARCHAR(10))
EXEC(@SQLString)

假如語句反復履行，則即便唯一的差別是為參數所供給的值分歧，每次履行時也必需生玉成新的 Transact-SQL 字符串。從而鄙人面幾個方面發生額定的開支：

SQL Server 查詢優化用具有將新的 Transact-SQL 字符串與現有的履行籌劃婚配的才能，此才能被字符串文本中赓續更改的參數值妨害，特殊是在龐雜的 Transact-SQL 語句中。
每次履行時均必需從新生成全部字符串。
每次履行時必需將參數值（不是字符或 Unicode 值）投影到字符或 Unicode 格局。

sp_executesql 支撐與 Transact-SQL 字符串相自力的參數值的設置：

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

/* Build the SQL string once. */
SET @SQLString =
   N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@level tinyint'

/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
           @level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
           @level = @IntVariable

此 sp_executesql 示例完成的義務與後面的 EXECUTE 示例所完成的雷同，但有以下額定長處：

由於 Transact-SQL 語句的現實文本在兩次履行之間未轉變，所以查詢優化器應當能將第二次履行中的 Transact-SQL 語句與第一次履行時生成的履行籌劃婚配。如許，SQL Server 不用編譯第二條語句。
Transact-SQL 字符串只生成一次。
整型參數按其自己格局指定。不須要轉換為 Unicode。

解釋為了使 SQL Server 從新應用履行籌劃，語句字符串中的對象稱號必需完整相符請求。

從新應用履行籌劃

在 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 sysdatabases WHERE dbid > 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')'
  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）的運用法式得以應用 sp_executesql 所供給的優勢。定位於 SQL Server 2000 的現有的 ODBC 運用法式不須要重寫便可以主動取得機能增益。有關更多信息，請拜見應用語句參數。

用於 SQL Server 的 Microsoft OLE DB 供給法式也應用 sp_executesql 直接履行帶有綁定參數的語句。應用 OLE DB 或 ADO 的運用法式不用重寫便可以取得 sp_executesql 所供給的優勢。

1、履行帶輸入參數的組合sql

declare @Dsql nvarchar(1000),
  @Name varchar(50),
  @TablePrimary varchar(50),
  @TableName varchar(50),
  @ASC int

set @TablePrimary='ID';
set @TableName='fine';
set @ASC = 1;

set @Dsql =N'select @Name = '+@TablePrimary+N' from '+@TableName+N' order by '+@TablePrimary+ (case @ASC when '1' then N' DESC ' ELSE N' ASC ' END)
print @Dsql

Set Rowcount 7
exec sp_executesql @Dsql,N'@Name varchar(50) output',@Name output
print @Name
Set Rowcount 0

2、履行帶輸出參數的組合sql

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

/* Build the SQL string once. */
SET @SQLString =
   N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@level tinyint'

/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
           @level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
           @level = @IntVariable