Share via

  • 發行項

在執行 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.Person WHERE BusinessEntityID = ?

ADO.NET

@<參數名稱>

@<參數名稱>

SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = @parmBusinessEntityID

ODBC

?

1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ?

EXCEL 和 OLE DB

?

0, 1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ?

搭配 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，從 AdventureWorks2008R2 中的 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 命令的範例。這些範例會執行 AdventureWorks2008R2 中的 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 設計師中設定之參數和傳回碼屬性的詳細資訊，請按下列主題：

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

外部資源

Integration Services 圖示 (小) 掌握 Integration Services 的最新狀態

若要取得 Microsoft 的最新下載、文件、範例和影片以及社群中的精選解決方案，請瀏覽 MSDN 或 TechNet 上的 Integration Services 頁面：

若要得到這些更新的自動通知，請訂閱該頁面上所提供的 RSS 摘要。