執行 SQL 工作

發行項
12/15/2008

更新： 2007 年 9 月 15 日

「執行 SQL」工作會執行封裝中的 SQL 陳述式或預存程序。工作可以包含逐次執行的單一 SQL 陳述式或多重 SQL 陳述式。您可將執行 SQL 工作用於下列用途：

截斷資料表或檢視，為插入資料做準備。
建立、改變和卸除資料庫物件，例如資料表和檢視。
將資料載入至事實 (Fact) 和維度 (Dimension) 資料表之前，先重建這些資料表。
執行預存程序。
將從查詢傳回的資料列集儲存到變數中。

您可以利用下列方式設定執行 SQL 工作：

指定用來連接到資料庫的連接管理員類型。
指定 SQL 陳述式傳回的結果集類型。
指定 SQL 陳述式的逾時。
指定 SQL 陳述式的來源。
指示工作是否要略過 SQL 陳述式的準備階段。
如果使用 ADO 連接類型，您必須指出 SQL 陳述式是否為預存程序。如果是其他連接類型，此屬性為唯讀，且其值固定為 false。

執行 SQL 工作可搭配「Foreach 迴圈」容器和「For 迴圈」容器，用來執行多個 SQL 陳述式。這些容器會實作封裝中重複的控制流程，並且可以重複執行「執行 SQL」工作。例如，若使用「Foreach 迴圈」容器，封裝即可列舉資料夾中的檔案，並重複執行「執行 SQL」工作，以便執行每個檔案中儲存的 SQL 陳述式。

連接到資料來源

執行 SQL 工作可使用不同類型的連接管理員，以連接到其執行 SQL 陳述式或預存程序的資料來源。此工作可使用下表中列出的連接類型。

連接類型	連接管理員
EXCEL	Excel 連接管理員
OLE DB	OLE DB 連接管理員
ODBC	ODBC 連接管理員
ADO	ADO 連接管理員
ADO.NET	ADO.NET 連接管理員
SQLMOBILE	SQL Server Compact Edition 連接管理員

建立 SQL 陳述式

此工作所使用的 SQL 陳述式來源，可以是包含陳述式的工作屬性、包含一或多個陳述式的檔案連接，或者包含陳述式的變數名稱。SQL 陳述式必須以來源資料庫管理系統 (DBMS) 的用語撰寫。

如果 SQL 陳述式儲存在檔案中，則工作會使用「檔案」連接管理員連接到該檔案。如需詳細資訊，請參閱＜檔案連接管理員＞。

在「SSIS 設計師」中，您可以使用 [執行 SQL 工作編輯器] 對話方塊輸入 SQL 陳述式，或使用圖形使用者介面 [查詢產生器] 建立 SQL 查詢。

附註：
執行 SQL 工作可能無法成功剖析在執行 SQL 工作外部撰寫的有效 SQL 陳述式。

在批次中傳送多重陳述式

如果您在執行 SQL 工作中加入多個陳述式，可將它們組成群組，並在批次中執行。若要表示批次結束，請使用 GO 命令。兩個 GO 命令之間的所有 SQL 陳述式，都會在一個批次中傳送至要執行的 OLE DB 提供者。SQL 命令可包含以 GO 命令分隔的多個批次。

您可分組到同一個批次中的 SQL 陳述式有其限制。如需詳細資訊，請參閱＜Batches of Statements＞。

如果執行 SQL 工作執行一個批次的 SQL 陳述式，則下列規則會套用至該批次︰

只有一個陳述式可傳回結果集，且它必須是批次中的第一個陳述式。
如果結果集使用結果繫結，則查詢必須傳回相同數目的資料行。如果查詢傳回不同數目的資料行，工作便會失敗。不過，即使工作失敗，它執行的查詢 (例如，DELETE 或 INSERT 查詢) 仍會成功。
如果結果繫結使用資料行名稱，則查詢必須傳回名稱與工作所使用之結果集名稱相同的資料行。如果資料行遺失，工作便會失敗。
如果工作使用參數繫結，則批次中所有查詢的參數數目和類型都必須相同。

執行參數化的 SQL 命令

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

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

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

依據執行 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.NET 連接管理員要求 SQL 命令必須使用參數名稱作為參數標記。這表示變數可以直接對應到參數。例如，@varName 變數會對應到名為 @parName 的參數，並提供值給 @parName 參數。

具有 EXCEL、ODBC 和 OLE DB 連接管理員的參數

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

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

如果「執行」工作使用 OLE DB 連接類型，工作的 BypassPrepare 屬性即可使用。如果「執行 SQL」工作使用含有參數的 SQL 陳述式，則您應該將這個屬性設為 true。

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

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

具有 ADO 連接管理員的參數

ADO 連接管理員要求 SQL 命令必須使用問號 (?) 作為參數標記，但是您可以使用整數值以外的任何使用者自訂名稱作為參數名稱。

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

搭配 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 和 @CheckDate 輸入參數。

連接類型	EXEC 語法
EXCEL 和 OLEDB	`EXEC uspGetBillOfMaterials ?, ?`
ODBC	`{call uspGetBillOfMaterials(?, ?)}` 如需 ODBC 呼叫語法的詳細資訊，請參閱 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 命令範例。所有的範例都使用輸入參數。如何搭配 ReturnValue 參數使用參數標記和參數名稱的規則，與適用於 Input 和 Output 參數類型的規則相同。

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

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

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

指定結果集類型

根據 SQL 命令的類型而定，結果集可能會，也可能不會傳回到執行 SQL 工作。例如，SELECT 陳述式通常會傳回結果集，INSERT 陳述式則不會。來自 SELECT 陳述式的結果集可包含零個資料列、一個資料列或多個資料列。預存程序亦可傳回整數值，稱為傳回碼，以表示程序的執行狀態。在此情況下，結果集是由單一資料列所組成。

執行 SQL 工作支援下列類型的結果集︰

無：結果集是在查詢未傳回任何結果時使用。例如，此結果集用於加入、變更和刪除資料表中記錄的查詢。
單一資料列：結果集是在查詢只傳回一個資料列時使用。例如，此結果集用於傳回傳回碼的預存程序，或用於傳回計數或總和的 SELECT 陳述式。
完整結果集：結果集是在查詢傳回多個資料列時使用。例如，此結果集用於擷取資料表中所有資料列的 SELECT 陳述式。
XML：結果集是在查詢傳回 XML 格式的結果集時使用。例如，此結果集用於包含 FOR XML 子句的 SELECT 陳述式。

如果執行 SQL 工作使用 [完整結果集] 結果集，且查詢傳回多個資料列集，則工作只會傳回第一個資料列集。如果此資料列集產生錯誤，則工作會報告該錯誤。如果其他資料列集產生錯誤，則工作不會報告它們。

執行 SQL 工作會將還不是字串之 SQL 陳述式所傳回的任何值轉換成字串。例如，SQL Server uniqueidentifier、bigint、decimal 或 numeric 資料類型的值會轉換為字串。

以結果集填入變數

如果查詢傳回的結果集類型為單一資料列、資料列集或 XML，則您可將結果集繫結至使用者自訂的變數。

如果結果集類型為 Single row，則可以藉由使用資料行名稱作為結果集名稱將傳回結果中的資料行繫結至變數，或者可以使用資料行清單中資料行的序數位置作為結果集名稱。例如，查詢 SELECT Color FROM Production.Product WHERE ProductID = ? 的結果集名稱可以為 Color 或 0。如果查詢傳回多個資料行，並且您要存取所有資料行中的值，則您必須將每個資料行繫結至不同的變數。如果您透過使用數字作為結果集名稱將資料行對應至變數，則該數字會反映資料行在查詢的資料行清單中所顯示的順序。例如，在查詢 SELECT Color, ListPrice, FROM Production.Product WHERE ProductID = ? 中，您將 0 用於 Color 資料行，將 1 用於 ListPrice 資料行。使用資料行名稱作為結果集名稱的功能取決於將該工作設定為使用的提供者。並非所有的提供者可以使用資料行名稱作為結果集名稱。

部分傳回單一值的查詢可能不包含資料行名稱。例如，SELECT COUNT (*) FROM Production.Product 陳述式不會傳回任何資料行名稱。您可以使用序數位置 0 作為結果名稱來存取傳回的結果。若要依據資料行名稱存取傳回的結果，查詢必須包含用來提供資料行名稱的 AS <別名名稱> 子句。SELECT COUNT (*)AS CountOfProduct FROM Production.Product 陳述式會提供 CountOfProduct 資料行。接著您可以使用 CountOfProduct 資料行名稱或序數位置 0 來存取傳回結果資料行。

如果結果集類型為完整結果集或 XML，則必須使用 0 作為結果集名稱。

當您將變數對應至結果集類型為 Single row 的結果集時，此變數須具有與結果集包含的資料行之資料類型相容的資料類型。例如，包含資料類型為 String 之資料行的結果集不能對應至數值資料類型的變數。XML 結果集只可對應至資料類型為 String 或 Object 的變數。如果變數有 String 資料類型，則執行 SQL 工作會傳回字串，且 XML 來源可使用 XML 資料。如果變數有 Object 資料類型，則執行 SQL 工作會傳回「文件物件模組 (DOM)」物件。[完整結果集] 必須對應至資料類型為 Object 的變數。傳回結果為資料列集物件。您可以撰寫導覽資料列集物件，以及存取資料行相關資訊和資料列集中相關資料的自訂工作。

下表列出可對應至結果集之變數的資料類型。

結果集類型	變數的資料類型	物件類型
單一資料列	與結果集之類型資料行相容的任何類型。	不適用
完整結果集	Object	如果工作使用原生連接管理員，包括 ADO、OLE DB、Excel 與 ODBC 連接管理員，則傳回的物件是 ADO Recordset。如果工作使用 Managed 連接管理員，例如 ADO.NET 連接管理員，那麼傳回的物件是 System.Data.DataSet。
XML	String	String
XML	Object	如果工作使用原生連接管理員，包括 ADO、OLE DB、Excel 與 ODBC 連接管理員，則傳回的物件是 MSXML6.IXMLDOMDocument。如果工作使用 Managed 連接管理員，例如 ADO.NET 連接管理員，那麼傳回的物件是 System.Xml.XmlDocument。

變數可在執行 SQL 工作或封裝範圍內定義。如果變數包含封裝範圍，則結果集可用於該封裝內的其他工作和容器，並可用於「執行封裝」或「執行 DTS 2000 封裝」工作所執行的任何封裝。

當您將變數對應到**「單一資料列」**結果集時，SQL 陳述式傳回的值以及不是字串的值，會轉換成字串。至於是否發生這項轉換，或者這項轉換是否為明確發生或為隱含發生，會根據所使用的連接管理員類型而定：

如果是 ADO.NET 連接管理員，則不會發生轉換。
如果是 ADO 或 ODBC 連接管理員，則會隱含發生轉換。
如果是 OLE DB 或 Excel 連接管理員，則連接管理員會明確地將 DBTYPE_I8、DBTYPE_UI8、DBTYPE_NUMERIC、DBTYPE_GUID 及 DBTYPE_BYTES 這些類型轉換成字串。

如需將結果集載入變數中的詳細資訊，請參閱＜如何：在執行 SQL 工作中將結果集對應至變數＞。