問(wèn)題描述
我有三個(gè)程序 MainProcedure,Procedure1,Procedure2
I have three Procedures MainProcedure,Procedure1,Procedure2
1) 在程序 1 中，我只有一個(gè)選擇語(yǔ)句，
1) In Procedure1 I just have a select statement ,
2) 在程序 2 中調(diào)用程序 1 并將輸出插入 #table
2) In Procedure2 am calling the Procedure1 and inserting the Output to a #table
3) 在主程序中，我正在調(diào)用程序 2 并嘗試將輸出插入到引發(fā)錯(cuò)誤的 #table 中
3) In the main Procedure I am calling the Procedure2 and iam trying to insert the Output to a #table which throws an error
消息 8164，級(jí)別 16，狀態(tài) 1，過(guò)程程序 2，第 10 行INSERT EXEC 語(yǔ)句不能嵌套.
我可以使用 Openrowset 解決這個(gè)問(wèn)題，我需要使用指定服務(wù)器名稱，有沒(méi)有其他方法可以通過(guò)不指定服務(wù)器名稱詳細(xì)信息來(lái)解決這個(gè)問(wèn)題
I can resolve this using Openrowset where I need to use specify Server Name ,is there any other way to solve this by not specifying the servername details
請(qǐng)查找示例程序以供參考
please find the sample procedure for reference
Create Proc Procedure1
As
Begin
Select 'Arun' Name, 'Pollachi' Place
Union
Select 'Vedaraj' Name, 'Devakottai' Place
End
Go
Create Proc Procedure2
As
Begin
Create Table #Table1
(
Name Varchar(50), Place Varchar(50)
)
INSERT #Table1
Exec Procedure1
SELECT 'Procedure2' [Source], * FROM #Table1
DROP TABLE #Table1
End
Go
Create Proc MainProcedure
As
Begin
Create Table #Table1
(
[Source] Varchar(50), Name Varchar(50), Place Varchar(50)
)
INSERT #Table1
Exec Procedure2
select * from #Table1
DROP TABLE #Table1
End
Go
任何人都可以更改我的主要程序并使其執(zhí)行謝謝！！
can any one change my main procedure and make it to get executed Thanks!!
推薦答案
就像你說(shuō)的，openrowset 可以工作，但除此之外我能想到的唯一方法是:
Like you said, openrowset will work, but other than that the only ways I can think of would be:
- 將 proc 1 和 proc 2 都更改為基于表的函數(shù)
- 將 proc 2 更改為 CLR 并將所有邏輯放在那里
- 將表作為表值參數(shù)傳遞
這里有更多關(guān)于這個(gè)原因的信息:
There's more info about the reasoning for this here:
https://connect.microsoft.com/SQLServer/feedback/details/294571/improve-insert-exechttp://dataeducation.com/revisiting-isnull-coalesce-and-the-perils-of-micro-optimization/
這篇關(guān)于INSERT EXEC 語(yǔ)句不能嵌套的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！