2010年5月12日 星期三

接 store procedure 的回傳結果

如果已經有人寫好了一支共用 store procedure,他回傳值可能是:

欄位A   欄位B   欄位C
====     ====     ====
A001     A002    A003
B001     B002    B003
C001     C002   C003

那我們可能會建一個暫存資料表
去接收 stroe procedure 的回傳值。

create table #tmp ( fieldA  nvarchar(max), fieldB nvarchar(max), fieldC nvarchar(max) )

insert into #tmp ( fieldA, fieldB, fieldC)
exec mystoreprocedure

然而,如果共用的 store procedure 回傳了30 幾個欄位,如果用上述方法,就得建立一個30幾個欄位的暫存資料表。這作法真的會讓人很無力,且容易發生「 An INSERT EXEC statement cannot be nested 的錯誤訊息[注3]」。所以這情況,可以考慮 OPENROWSET[注1] 來完成。雖然共用的 store procedure 回傳了30幾個欄位,但我真正會使用的,可能只有其中二、三個。所以可以寫成:


SELECT com_empno,com_cname FROM
OPENROWSET('SQLOLEDB',
'.';'sql_account';'sql_password',
'exec swapdb.dbo.pr_Find_ITRI_BossID')

此外,有個重要的地方需注意,就是使用  OPENROWSET 時的查詢字串,一定要「常數」,不可以是「變數」。因此,如果你的共用 store procedure 需要傳參數進去,則不可以將查詢字串寫成  'exec swapdb.dbo.pr_Find_ITRI_BossID  '+@seqsn   (參考[注2])

如果遇到需要傳參數的 store procedure,則可以透過先宣告一個字串變數( declare @str),來組出 OPENROWSET 的所有指令,最後再用 exec 去執行它 ( exec (@str) )來完成,這樣就可以避開 OPENROWSET 的查詢字串,一定要「常數」的限制。



參考:
[注1]OPENROWSET (Transact-SQL)
[注2]OpenDataSource、OPENQUERY、OPENROWSET用法
[注3]An INSERT EXEC statement cannot be ne...
[注4]感謝 Ken 熱心指導

沒有留言:

張貼留言