在撰寫預設程序(store procedure, SP)時
有時候會在當中呼叫其他的SP取得資料在進行加工
不時會發生 An INSERT EXEC statement cannot be nested 的問題
這幾天在撰寫一個共用SP來產生所要的資料時剛好遇到
來分享一下所遇到的狀況以及解決的方法
問題
在主SP中先宣告一個temp table #TABLE_A
在互叫共用SP sp_Common 吐出資料放入#TABLE_A
CREATE TABLE #TABLE_A
(
...
)
INSERT INTO #TABLE_A
EXEC dbo.sp_Common
發生了An INSERT EXEC statement cannot be nested
發現
在sp_Common中,有使用動態語法存放進一個Temp Table
然後再進行加工後才吐出來放到主SP中的#TABLE_A時出錯
解法
1. 改寫sp_Common中的寫法,避免使用動態語法INSERT EXEC的方法
2. 直接使用EXEC動態語法,而不再暫存加工
3. sp_Common吐出來的Table不存放在Temp Table中
總結
SQL Server在Store procedure中
為了避免SP互Call當中又有動態語法的情況造成無限迴圈
(預測是擔心撰寫不小心或是被SQL injection而使DB掛掉)
所以禁止動態語法暫存在其他SP互叫又暫存
雖然目前SQL Server有提供動態語法多次呼叫而預計評估計畫也不會重複編寫的狀況
但小弟還是對於動態語法不是很喜歡,真的沒到最後關頭還是不要使用為妙(個人淺見)
留言列表