共用方式為

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

  • 發行項

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

注意

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

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

不過，在執行 SQL 工作中使用參數和傳回碼比只是知道工作支援的參數類型，以及如何對應這些參數還要複雜。 若要在執行 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 datetimeoffset、string或datetime2。ReturnValue 如果您選取數據類型為 string 或 datetime2的參數，Integration Services 會將數據轉換成字串或 datetime2。

  • ADO 連線管理員要求time或資料儲存在參數類型為 Input 或 datetimeoffset 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之數據的特定記憶體需求。 您必須以下列其中一種參數類型儲存此資料：

  • input SQL_WVARCHAR數據類型的參數

  • 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，從 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)。

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

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

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

設定工作或容器的屬性

相關內容

另請參閱

執行 SQL 工作
執行 SQL 工作中的結果集