在預存程序中封裝資料轉換

已完成

雖然只要您需要轉換資料，就可以在指令碼中執行 CREATE EXTERNAL TABLE AS SELECT (CETAS) 陳述式，但最好是在預存程序中封裝轉換作業。 此方法可讓您在單一程序呼叫中提供參數、擷取輸出，以及包含其他邏輯，以便更輕鬆地設定資料轉換運作。

例如，下列程式碼所建立的預存程序會在外部資料表已經存在時，卸除該資料表，然後再使用指定年份的訂單資料重建該資料表：

CREATE PROCEDURE usp_special_orders_by_year @order_year INT
AS
BEGIN

	-- Drop the table if it already exists
	IF EXISTS (
                SELECT * FROM sys.external_tables
                WHERE name = 'SpecialOrders'
            )
        DROP EXTERNAL TABLE SpecialOrders

	-- Create external table with special orders
	-- from the specified year
	CREATE EXTERNAL TABLE SpecialOrders
		WITH (
			LOCATION = 'special_orders/',
			DATA_SOURCE = files,
			FILE_FORMAT = ParquetFormat
		)
	AS
	SELECT OrderID, CustomerName, OrderTotal
	FROM
		OPENROWSET(
			BULK 'sales_orders/*.csv',
			DATA_SOURCE = 'files',
			FORMAT = 'CSV',
			PARSER_VERSION = '2.0',
			HEADER_ROW = TRUE
		) AS source_data
	WHERE OrderType = 'Special Order'
	AND YEAR(OrderDate) = @order_year
END

注意

如先前所述，卸除現有的外部資料表並不會刪除包含其資料檔案的資料夾。 如果在執行預存程序之前，目標資料夾已經存在，您必須明確刪除該檔案夾，否則會發生錯誤。

除封裝 Transact-SQL 邏輯外，預存程序也提供下列優點：

減少用戶端的伺服器流量

程序中的命令會以單一程式碼批次的形式執行；這可大幅減少伺服器與用戶端之間的網路流量，因為只有執行該程序的呼叫會透過網路傳送。

提供安全性界線

多個使用者和用戶端程式都可以透過程序，在基礎資料庫物件上執行作業，即使使用者和程式不具備這些基礎物件的直接權限亦可。 此程序會控制執行的流程和活動，並保護基礎資料庫物件；不再需要授與個別物件層級的權限，且可簡化安全層。

簡化維護工作

與資料轉換相關的邏輯或檔案系統位置中的任何變更，只能套用至預存程序；不需要更新用戶端應用程式或其他呼叫函式。

提升效能

預存程序會在第一次執行時組建，而產生的執行計畫會保留在快取中，並在後續執行相同的預存程序時重複使用。 因此，處理程序所花費的時間較少。