模擬 SELECT * FROM Procedure (預存程序) 的二次資料使用或子查詢

模擬 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 的方式。
就記錄下來,未來在看到什麼方式,在回來補充這類的資訊。