在執行 SQL 工作中使用參數和傳回碼

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

[!附註]

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

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

不過，在執行 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

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

搭配 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

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

在 WHERE 子句中使用參數

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

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

下表依照連接管理員類型列出 SELECT 命令的範例。INSERT、UPDATE 和 DELETE 陳述式與這些範例類似。這些範例使用 SELECT，從 AdventureWorks 中的 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 命令的範例。這些範例會執行 AdventureWorks 中的 uspGetBillOfMaterials 預存程序。預存程序會使用 @StartProductID 和 @CheckDateinput 參數。

連接類型	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 預存程序使用輸入和輸出參數的詳細資訊，請參閱＜參數 (Database Engine)＞、＜使用 OUTPUT 參數傳回資料＞和＜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)＞。

在執行 SQL 工作中設定參數和傳回碼

如需有關可以在 SSIS 設計師中設定之參數和傳回碼屬性的詳細資訊，請按下列主題：

• 執行 SQL 工作編輯器 (參數對應頁面)

如需有關如何在 SSIS 設計師中設定這些屬性的詳細資訊，請按一下下列主題：

• 如何：設定工作或容器的屬性

外部資源

• 位於 blogs.msdn.com 的部落格項目：使用輸出參數的預存程序 (英文)

• 位於 msftisprodsamples.codeplex.com 的 CodePlex 範例：執行 SQL 參數和結果集 (英文)

掌握 Integration Services 的最新狀態 若要取得 Microsoft 的最新下載、文件、範例和影片以及社群中的選定解決方案，請瀏覽 MSDN 或 TechNet 上的 Integration Services 頁面： 瀏覽 MSDN 上的 Integration Services 頁面 瀏覽 TechNet 上的 Integration Services 頁面 若要得到這些更新的自動通知，請訂閱該頁面上所提供的 RSS 摘要。

請參閱

概念

執行 SQL 工作

在執行 SQL 工作中使用結果集