模擬 SELECT * FROM Procedure (預存程序) 的二次資料使用或子查詢
在 MSDN Forum 的這篇討論 預存程序的結果如何再利用 ,最後整理以下幾種方式:
1.使用暫存的資料表
參考 [TSQL]模擬 SELECT FROM Stored Procedure
CREATE Table #Temp
(
Customer varchar(1000)
)
-------------- 透過此方式將 Stored Procedure 資料寫入 暫存的資料表
Insert into #Temp
-- SP_GetOrders 你那個Stored Procedure名稱
EXEC SP_GetOrders
優點:使用區域變數就可以達成,而且不會太複雜。
缺點:需要固定格式的SELECT,因為CREATE TABLE 需要先給予欄位
2.使用 View 的方式
參考 Convert a Stored Procedure to a View TSQL procedure
優點:欄位可以動態,透過 SELECT * 的方式。
缺點:但參考的連結所使用的方式,類似跨查詢的方式,需要在開OPENROWSET安全性。
3.將 Procedure 改為 Function 方式,傳回值改為 Table 變數
參考 資料表值函數 http://msdn.microsoft.com/zh-tw/library/ms177499%28SQL.90%29.aspx
USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
WHERE SH.CustomerID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
SELECT * FROM Sales.ufn_SalesByStore(602)
WHERE Productid < 742
優點:比較簡潔,不需要用到暫時變數。
缺點:要串 exec 這種就不太適合。
結論:
之前遇到的情境,都會用 1暫存資料表 的方式,今天看到 3 Function 資料表變數的方法,比較會偏向使用 3 的方式。
就記錄下來,未來在看到什麼方式,在回來補充這類的資訊。