sp_executesql (Transact-SQL)
適用於:Microsoft Fabric 中 Microsoft Fabric倉儲中的 SQL ServerAzure SQL 資料庫 Azure SQL 受控執行個體 Azure SynapseAnalytics Analytics Platform System (PDW)SQL 分析端點
執行可重複使用多次的 Transact-SQL 語句或批次,或動態建立的語句。 Transact-SQL 語句或批次可以包含內嵌參數。
重要
運行時間編譯的 Transact-SQL 語句可能會向惡意攻擊公開應用程式。
語法
-- Syntax for SQL Server, Azure SQL Database, Azure Synapse Analytics, Parallel Data Warehouse
sp_executesql [ @stmt = ] statement
[
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }
]
引數
[ @stmt= ] statement
是一個 Unicode 字串,其中包含 Transact-SQL 陳述式或批次。 @stmt 必須是 Unicode 常數或 Unicode 變數。 不允許使用比較複雜的 Unicode 運算式,如用 + 運算子來串連兩個字串。 不允許使用字元常數。 如果指定 Unicode 常數,必須在前面加上 N 前置詞。例如,Unicode 常數 N'sp_who' 是有效的,但字元常數 'sp_who' 無效。 字串大小只受到可用資料庫伺服器記憶體的限制。 在 64 位元伺服器中,字串大小限制為 2 GB,大小上限是由 nvarchar(max) 設定。
注意
@stmt 可以包含與變數名稱格式相同的參數,例如:N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'
在 @params 參數定義清單和參數值清單中,@stmt 所包含的每個參數都必須有對應的項目。
[ @params= ]N'@parameter_name data_type [ ,... n ] '
是一個字串,其中包含內嵌在 @stmt 中的所有參數定義。字串必須是 Unicode 常數或 Unicode 變數。 每個參數定義都由參數名稱和資料類型組成。 n 是一個預留位置,表示其他參數定義。 @stmt中指定的每個參數都必須在 @params 中定義。 如果 Transact-SQL 陳述式或 @stmt 中的批次不包含參數,則不需要 @params。 這個參數的預設值是 NULL。
[ @param1= ] 'value1'
這是參數字串所定義的第一個參數的值。 這個值可以是 Unicode 常數或 Unicode 變數。 必須為 @stmt 所含的每個參數提供參數值。當 Transact-SQL 陳述式或 @stmt 中的批次沒有參數時,就不需要這些值。
[ OUT |OUTPUT ]
指出這個參數是輸出參數。 text、 ntext 和 image 參數可以做為 OUTPUT 參數,除非程式是 Common Language Runtime (CLR) 程式。 除非程式是 CLR 程式,否則使用 OUTPUT 關鍵詞的輸出參數可以是數據指標佔位元元。
n
這是其他參數值的預留位置。 這些值只能是常數或變數。 這些值不能是比較複雜的運算式,如函數或利用運算子來建立的運算式。
傳回碼值
0 (成功) 或非零 (失敗)
結果集
從 SQL 字串內建的所有 SQL 語句傳回結果集。
備註
sp_executesql參數必須依本主題稍早的一節所述,以特定順序輸入。 如果未按順序輸入參數,就會出現錯誤訊息。
sp_executesql與 EXECUTE 在批次、名稱和資料庫內容方面的行為相同。 執行 sp_executesql 語句之前,不會編譯 sp_executesql @stmt 參數中的 Transact-SQL 語句或批次。 然後,@stmt的內容會編譯和執行為執行計劃,與呼叫 sp_executesql 之批次的執行計劃分開。 sp_executesql批次無法參考在呼叫 sp_executesql 的批次中宣告的變數。 呼叫sp_executesql的批次看不到sp_executesql批次中的本機數據指標或變數。 資料庫內容中的變更只會持續到 sp_executesql 語句的結尾。
當參數值變更為語句的唯一變化時,可以使用 sp_executesql,而不是預存程式來執行 Transact-SQL 語句多次。 由於 Transact-SQL 語句本身會維持不變,而且只有參數值變更,所以 SQL Server 查詢優化器可能會重複使用它為第一次執行所產生的執行計劃。
注意
若要改善效能,請使用語句字串中的完整物件名稱。
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 BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM AdventureWorks2022.HumanResources.Employee
WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@BusinessEntityID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@BusinessEntityID = @IntVariable;
輸出參數也可以與sp_executesql搭配使用。 下列範例會從 HumanResources.Employee
範例資料庫中的 AdventureWorks2022
數據表擷取職稱,並在輸出參數 @max_title
中傳回它。
DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @max_title VARCHAR(30);
SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)
FROM AdventureWorks2022.HumanResources.Employee
WHERE BusinessEntityID = @level';
SET @ParmDefinition = N'@level TINYINT, @max_titleOUT VARCHAR(30) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;
能夠取代 sp_executesql 中的參數,可提供下列優點,以使用 EXECUTE 語句來執行字串:
由於sp_executesql字串中 Transact-SQL 語句的實際文字不會在執行之間變更,因此查詢優化器可能會比對第二次執行中的 Transact-SQL 語句,以及針對第一次執行產生的執行計劃。 因此,SQL Server 不需要編譯第二個語句。
Transact-SQL 字串只會建置一次。
整數參數是以原生格式指定。 不需要轉換成 Unicode。
權限
需要 public 角色中的成員資格。
範例
A. 執行簡單的 SELECT 語句
下列範例會建立並執行簡單 SELECT
語句,其中包含名為的 @level
內嵌參數。
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorks2022.HumanResources.Employee
WHERE BusinessEntityID = @level',
N'@level TINYINT',
@level = 109;
B. 執行動態建置的字串
下列範例示範如何使用 sp_executesql
來執行動態建置的字串。 範例預存程式是用來將數據插入一組數據表,用來分割銷售數據一年。 每年每個月都有一個數據表,其格式如下:
CREATE TABLE May1998Sales
(OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL,
CHECK (DATEPART(mm, OrderDate) = OrderMonth)
)
這個範例預存程式會動態建置並執行 INSERT
語句,以將新訂單插入正確的數據表中。 此範例會使用順序日期來建置應該包含數據的數據表名稱,然後將該名稱併入 INSERT
語句中。
注意
這是sp_executesql的簡單範例。 此範例不包含錯誤檢查,而且不包含商務規則的檢查,例如保證數據表之間不會重複訂單號碼。
CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
@PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT
-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
/* Build the name of the table. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
'Sales' +
/* Build a VALUES clause. */
' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
' @InsOrdMonth, @InsDelDate)'
/* Set the value to use for the order month because
functions are not allowed in the sp_executesql parameter
list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)
EXEC sp_executesql @InsertString,
N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
@InsOrdMonth INT, @InsDelDate DATETIME',
@PrmOrderID, @PrmCustomerID, @PrmOrderDate,
@OrderMonth, @PrmDeliveryDate
GO
在此程式中使用sp_executesql比使用EXECUTE來執行字串更有效率。 使用sp_executesql時,每個月數據表只會產生12個版本的INSERT字串。 使用 EXECUTE 時,每個 INSERT 字串都是唯一的,因為參數值不同。 雖然這兩種方法會產生相同數目的批次,但sp_executesql所產生的 INSERT 字串相似度,使得查詢優化器更有可能重複使用執行計劃。
C. 使用 OUTPUT 參數
下列範例會使用 OUTPUT
參數,將語句所產生的 SELECT
結果集儲存在 參數中 @SQLString
。接著會執行兩個 SELECT
語句,以使用 參數的值 OUTPUT
。
USE AdventureWorks2022;
GO
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @SalesOrderNumber NVARCHAR(25);
DECLARE @IntVariable INT;
SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID';
SET @ParmDefinition = N'@CustomerID INT,
@SalesOrderOUT NVARCHAR(25) OUTPUT';
SET @IntVariable = 22276;
EXECUTE sp_executesql
@SQLString
,@ParmDefinition
,@CustomerID = @IntVariable
,@SalesOrderOUT = @SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;
-- This SELECT statement uses the value of the OUTPUT parameter in
-- the WHERE clause.
SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;
範例:Azure Synapse Analytics 和 Analytics Platform System (PDW)
D. 執行簡單的 SELECT 語句
下列範例會建立並執行簡單 SELECT
語句,其中包含名為的 @level
內嵌參數。
-- Uses AdventureWorks2022
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
WHERE EmployeeKey = @level',
N'@level TINYINT',
@level = 109;
另請參閱
意見反應
https://aka.ms/ContentUserFeedback。
即將登場:在 2024 年,我們將逐步淘汰 GitHub 問題作為內容的意見反應機制,並將它取代為新的意見反應系統。 如需詳細資訊,請參閱:提交並檢視相關的意見反應