有在碰資料庫的人應該都知道,我們可以透過預存程序(Store Procedure)來封裝資料查詢,讓資料服務更效率且更容易被管理,但做成預存程序後,就無法用簡單的查詢語法 SELECT * FROM TableName 來做基本的資料檢視。Google 了許久,發現一招,透過 OPENROWSET 來簡單達成查詢需求,真的又快又方便(但不建議用在 Production)。

用函數來封裝

如果是用函數(Function)的方式來封裝,那就沒有查詢的問題了,可以直接用 SELECT * FROM FunctionName() 來達成。

這篇文章建議我們在預存程序符合下列條件時,可以將預存程序重寫為函數。

  • 單一 SELECT 陳述式中的邏輯是屬於可運算的,但因為它需要參數,所以是預存程序而非檢視。這個情況可以利用內嵌資料表數值函數來處理。
  • 預存程序不執行更新作業 (資料表變數除外)。
  • 不需要動態的 EXECUTE 陳述式
  • 預存程序會傳回一個結果集。
  • 預存程序的主要目的是要建立載入暫存資料表的立即結果,以便在 SELECT 陳述式中接受查詢。

Stored Procedures 與 Functions 的差異

CALL In Expression Return
Stored Procedures O X X
Functions X O O

常見的做法

最直覺的做法就是把預存程序吐出來的資料,存放在一個暫存的資料表,然後我們再對暫存的資料表做查詢。參考作法如下:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

但缺點是你要先建出一張欄位和來源相同的資料表,有點麻煩。

使用 OPENROWSET

MSDN 文件可以查到,從 SQL Server 2005 開始就支援 OPENROWSET 這函數 ,這是 OPENQUERY 外,另一個存取連結伺服器資料表的方法,而且是使用 OLE DB 來連接和存取資料的單次特定方法。

透過 OPENROWSET 可以讓查詢語法變得很簡單,如下:

SELECT * 
FROM OPENROWSET ('SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=YES;', 'EXEC MyStoredProcedure')
  • 第一個參數是 Provider Name,表示使用 ‘SQLOLEDB’ 來連結資料庫
  • 第二個參數是連線字串,這裡表示使用本地端的伺服器並使用整合式驗證
  • 第三個參數是我們要執行的 SQL

這樣的做法是根據 OLE DB Provider 的能力而定,有可能會有效能或安全上的疑慮,因此不建議使用在正式環境上

OPENQUERY 也是一種類似的作法,但 OPENQUERY 只能對 LinkedServer 來做查詢,你必須先建立好連結。


參考資料:


Poy Chang

Trial and Error