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

2010-11-18

16486
0
SQL

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

回首頁