Louis學習筆記

在撰寫預設程序(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有提供動態語法多次呼叫而預計評估計畫也不會重複編寫的狀況

但小弟還是對於動態語法不是很喜歡，真的沒到最後關頭還是不要使用為妙(個人淺見)