July 30, 2009 at 1:27 am
CREATE PROC GetData
As
DECLARE @STR NVARCHAR(MAX)
SET @STR = N'SELECT 1,''XYZ''';
DECLARE @tempTab TABLE
(
ID INT,
[NAME] VARCHAR(100)
)
INSERT INTO @tempTab
EXEC SP_EXECUTESQL @STR
SELECT ID,[NAME] FROM @tempTab
GO
CREATE PROC FetchData
As
DECLARE @tempTab TABLE
(
ID INT,
[NAME] VARCHAR(100)
)
INSERT INTO @tempTab
EXEC GetData
SELECT * from @tempTab
GO
EXEC FetchData
When I try to execute the SP FetchData I get the error message as follows:
Msg 8164, Level 16, State 1, Procedure GetData, Line 12
An INSERT EXEC statement cannot be nested.
Any solution to this problem.
July 30, 2009 at 4:02 am
Why dont you wrap it as single procedure?
Failing to plan is Planning to fail
July 30, 2009 at 4:09 am
No, It can not be done. The SP GetData is a generic one and can be used by multiple SP's.It is also necessary to include the dynamic SQL query in it.
So the output of SP GetData is used by other SP's and this output is used to join with other tables to get the required data.
The problem occurs when we execute a dynamic SQL query inside a SP and call this SP in another one to insert data in a temporary table.
July 30, 2009 at 4:35 am
SP_EXECUTESQL executes in a different session and not in the session where it is called. i.e. if proc xyz contains a SP_EXECUTESQL statement then this is not executed in the proc xyz's session.
"Keep Trying"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply