執行 SQL 工作中的參數和傳回碼

發行項
05/07/2013

SQL 陳述式和預存程序經常使用 input 參數、output 參數以及傳回碼。在 Integration Services 中，執行 SQL 工作支援 Input、Output 和 ReturnValue 等參數類型。您可以使用 Input 類型當做輸入參數，使用 Output 當做輸出參數，並使用 ReturnValue 當做傳回碼。

[!附註]

只有在資料提供者支援參數時，您才能在執行 SQL 工作中使用參數。

SQL 命令中的參數 (包括查詢和預存程序) 都會對應到在執行 SQL 工作範圍內、父容器內或封裝範圍內建立的使用者自訂變數。變數值可於設計階段設定，或於執行階段動態擴展。您也可以將參數對應到系統變數。如需詳細資訊，請參閱＜Integration Services (SSIS) 變數＞和＜系統變數＞。

不過，在執行 SQL 工作中使用參數和傳回碼比只是知道工作支援的參數類型，以及如何對應這些參數還要複雜。若要在執行 SQL 工作中成功使用參數和傳回碼，需要有其他使用需求與指導方針。本主題的其餘部分包含這些使用需求和指導方針：

使用參數名稱和標記
搭配日期和時間資料類型使用參數
在 WHERE 子句中使用參數
搭配預存程序使用參數
取得傳回碼的值
在執行 SQL 工作編輯器中設定參數和傳回碼

使用參數名稱和標記

依據執行 SQL 工作使用的連接類型，SQL 命令的語法會使用不同的參數標記。例如，ADO.NET 連接管理員類型要求 SQL 命令必須使用格式為 @varParameter 的參數標記，而 OLE DB 連接類型則需要使用問號 (?) 參數標記。

在變數與參數的對應中，可以用來當做參數名稱的名稱也會隨著連接管理員的類型而異。例如，ADO.NET 連接管理員類型使用具有 @ 前置詞的使用者自訂名稱，而 OLE DB 連接管理員類型則要求您必須使用以 0 為基底的序數數值做為參數名稱。

下表摘要說明執行 SQL 工作可以使用之連接管理員類型的 SQL 命令需求。

連接類型	參數標記	參數名稱	範例 SQL 命令
ADO	?	Param1, Param2, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET	@<參數名稱>	@<參數名稱>	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC	?	1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
EXCEL 和 OLE DB	?	0, 1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

搭配 ADO.NET 和 ADO 連接管理員使用參數

ADO.NET 和 ADO 連接管理員對於使用參數的 SQL 命令，擁有特定的需求：

ADO.NET 連接管理員要求 SQL 命令必須使用參數名稱做為參數標記。這表示變數可以直接對應到參數。例如，@varName 變數會對應到名為 @parName 的參數，並提供值給 @parName 參數。
ADO 連接管理員要求 SQL 命令必須使用問號 (?) 做為參數標記。不過，您可以使用整數值之外的任何使用者自訂名稱做為參數名稱。

若要提供值給參數，變數會對應到參數名稱。接著，執行 SQL 工作會在參數清單中使用參數名稱的序數值，將值從變數載入參數中。

搭配 EXCEL、ODBC 和 OLE DB 連接管理員使用參數

EXCEL、ODBC 和 OLE DB 連接管理員要求 SQL 命令必須使用問號 (?) 做為參數標記，並以 0 或 1 為基底的數值作為參數名稱。如果執行 SQL 工作使用 ODBC 連接管理員，對應到查詢中第一個參數的參數名稱會被命名為 1，否則，該參數會被命名為 0。對於後續的參數，參數名稱的數值表示 SQL 命令中，參數名稱所對應的參數。例如，參數名稱 3 對應至第 3 個參數，這個參數在 SQL 命令中是由第 3 個問號 (?) 來代表。

為了將值提供給參數，變數會對應到參數名稱，而執行 SQL 工作則會使用參數名稱的序數值，將值從變數載入參數中。

依據連接管理員使用的提供者而定，部分 OLE DB 資料類型可能不受支援。例如，Excel 驅動程式只能辨識有限的一組資料類型。如需有關具有 Excel 驅動程式之 Jet 提供者行為的詳細資訊，請參閱＜Excel 來源＞。

搭配 OLE DB 連接管理員使用參數

執行 SQL 工作使用 OLE DB 連接管理員時，工作的 BypassPrepare 屬性即可使用。如果執行 SQL 工作搭配參數使用 SQL 陳述式，您應該將這個屬性設為 true。

您在使用 OLE DB 連接管理員時無法使用參數化的子查詢，因為執行 SQL 工作無法透過 OLE DB 提供者衍生參數資訊。不過，您可以使用運算式，將參數值串連到查詢字串，並設定工作的 SqlStatementSource 屬性。

搭配日期和時間資料類型使用參數

搭配 ADO.NET 和 ADO 連接管理員使用日期和時間參數

讀取 SQL Server 類型、time 和 datetimeoffset 的資料時，使用 ADO.NET 或 ADO 連接管理員的執行 SQL 工作具有下列額外需求：

若是 time 資料，ADO.NET 連接管理員要求此資料必須以參數類型為 Input 或 Output，且資料類型為 string 的參數儲存。
若是 datetimeoffset 資料，ADO.NET 連接管理員要求此資料必須以下列其中一個參數儲存：
- 參數類型為 Input，且資料類型為 string 的參數。
- 參數類型為 Output 或 ReturnValue，且資料類型為 datetimeoffset、string 或 datetime2 的參數。如果您選取資料類型為 string 或 datetime2 的參數，Integration Services 會將資料轉換為字串或 datetime2。
ADO 連接管理員要求 time 或 datetimeoffset 資料必須以參數類型為 Input 或 Output，且資料類型為 adVarWchar 的參數儲存。

如需有關 SQL Server 資料類型，以及如何將其對應到 Integration Services 資料類型的詳細資訊，請參閱＜資料類型 (Transact-SQL)＞和＜Integration Services 資料類型＞。

搭配 OLE DB 連接管理員使用日期和時間參數

使用 OLE DB 連接管理員時，執行 SQL 工作對於 SQL Server 資料類型、date、time、datetime、datetime2 和 datetimeoffset 的資料具有特定的儲存需求。您必須以下列其中一種參數類型儲存此資料：

NVARCHAR 資料類型的輸入參數。
具有適當資料類型的輸出參數，如下表所列示。

Output 參數類型

日期資料類型

DBDATE

date

DBTIME2

time

DBTIMESTAMP

datetime,datetime2

DBTIMESTAMPOFFSET

datetimeoffset

Output 參數類型	日期資料類型
DBDATE	date
DBTIME2	time
DBTIMESTAMP	datetime,datetime2
DBTIMESTAMPOFFSET	datetimeoffset

如果資料沒有以適當的輸入或輸出參數儲存，則封裝會失敗。

搭配 ODBC 連接管理員使用日期和時間參數

使用 ODBC 連接管理員時，執行 SQL 工作對於具有其中一種 SQL Server 資料類型、date、time、datetime、datetime2 和 datetimeoffset 的資料具有特定的儲存需求。您必須以下列其中一種參數類型儲存此資料：

SQL_WVARCHAR 資料類型的 input 參數。
具有適當資料類型的 output 參數，如下表所列示。

Output 參數類型

日期資料類型

SQL_DATE

date

SQL_SS_TIME2

time

SQL_TYPE_TIMESTAMP

-或-

SQL_TIMESTAMP

datetime,datetime2

SQL_SS_TIMESTAMPOFFSET

datetimeoffset

Output 參數類型	日期資料類型
SQL_DATE	date
SQL_SS_TIME2	time
SQL_TYPE_TIMESTAMP -或- SQL_TIMESTAMP	datetime,datetime2
SQL_SS_TIMESTAMPOFFSET	datetimeoffset

如果資料沒有以適當的輸入或輸出參數儲存，則封裝會失敗。

在 WHERE 子句中使用參數

SELECT、INSERT、UPDATE 和 DELETE 命令經常包含 WHERE 子句，以指定篩選條件，用以定義來源資料表中每個資料列必須符合才能做為 SQL 命令的條件。參數會在 WHERE 子句中提供篩選值。

您可以使用參數標記，動態提供參數值。 SQL 陳述式中可以使用的參數標記和參數名稱的規則，需視「執行 SQL」所使用的連接管理員類型而定。

下表依照連接管理員類型列出 SELECT 命令的範例。 INSERT、UPDATE 和 DELETE 陳述式與這些範例類似。這些範例使用 SELECT，從 AdventureWorks2012 中的 Product 資料表傳回 ProductID 大於及小於兩個參數所指定之值的產品。

連接類型	SELECT 語法
EXCEL、ODBC 和 OLEDB	SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO	SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO.NET	SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

這些範例必須使用具有下列名稱的參數：

EXCEL 和 OLED DB 連接管理員使用參數名稱 0 和 1。 ODBC 連接類型則使用 1 和 2。
ADO 連接類型可以使用任何兩個參數名稱，例如 Param1 和 Param2，但這些名稱必須對應它們在參數清單中的序數位置。
ADO.NET 連接類型使用參數名稱 @parmMinProductID 和 @parmMaxProductID。

搭配預存程序使用參數

執行預存程序的 SQL 命令亦可使用參數對應。如何使用參數標記和參數名稱的規則，需視「執行 SQL」所使用的連接管理員類型而定，這一點與參數化查詢的規則相同。

下表依照連接管理員類型列出 EXEC 命令的範例。這些範例會執行 AdventureWorks2012 中的 uspGetBillOfMaterials 預存程序。預存程序會使用 @StartProductID 和 @CheckDate input 參數。

連接類型	EXEC 語法
EXCEL 和 OLEDB	EXEC uspGetBillOfMaterials ?, ?
ODBC	{call uspGetBillOfMaterials(?, ?)} 如需有關 ODBC CALL 語法的詳細資訊，請參閱 MSDN Library 之《ODBC 程式設計人員參考》中的主題程序參數。
ADO	如果 IsQueryStoredProcedure 設為 False、EXEC uspGetBillOfMaterials ?, ? 如果 IsQueryStoredProcedure 設為 True、uspGetBillOfMaterials
ADO.NET	如果 IsQueryStoredProcedure 設為 False、EXEC uspGetBillOfMaterials @StartProductID, @CheckDate 如果 IsQueryStoredProcedure 設為 True、uspGetBillOfMaterials

若要使用輸出參數，此語法要求您必須在每個參數標記後面加上 OUTPUT 關鍵字。例如，下列輸出參數語法是正確的：EXEC myStoredProcedure ? OUTPUT。

如需有關搭配 Transact-SQL 預存程序使用輸入和輸出參數的詳細資訊，請參閱＜EXECUTE (Transact-SQL)＞。

取得傳回碼的值

預存程序可以傳回稱為傳回碼的整數值，以指出程序的執行狀態。若要在執行 SQL 工作中實作傳回碼，請使用 ReturnValue 類型的參數。

下表依據連接類型列出實作傳回碼的部分 EXEC 命令範例。所有的範例都使用 input 參數。如何使用參數標記和參數名稱的規則，在所有參數類型 (Input、Output 和 ReturnValue) 之中都相同。

部分語法不支援參數常值。在這種情況下，您必須使用變數來提供參數值。

連接類型	EXEC 語法
EXCEL 和 OLEDB	EXEC ? = myStoredProcedure 1
ODBC	{? = call myStoredProcedure(1)} 如需有關 ODBC CALL 語法的詳細資訊，請參閱 MSDN Library 之《ODBC 程式設計人員參考》中的主題程序參數。
ADO	如果 IsQueryStoreProcedure 設為 False、EXEC ? = myStoredProcedure 1 如果 IsQueryStoreProcedure 設為 True、myStoredProcedure
ADO.NET	如果 IsQueryStoreProcedure 設為 True。 myStoredProcedure

在上表顯示的語法中，「執行 SQL」工作使用 [直接輸入] 來源類型執行預存程序。「執行 SQL」工作也可以使用 [檔案連接] 來源類型執行預存程序。不論「執行 SQL」工作是使用 [直接輸入] 或 [檔案連接] 來源類型，都請使用 ReturnValue 類型的參數來實作傳回碼。如需有關如何設定「執行 SQL」工作所執行之 SQL 陳述式來源類型的詳細資訊，請參閱＜執行 SQL 工作編輯器 (一般頁面)＞。

如需有關搭配 Transact-SQL 預存程序使用傳回碼的詳細資訊，請參閱＜RETURN (Transact-SQL)＞。