CODEWITHSUNDEEP
sqlsql-server
user3635169
user3635169

Reputation: 25

stored procedure an insert exec statement cannot be nested

I have the following stored procedure which calls another stored procedure dbo.[spGetResult] 

;WITH CTE 
AS
(
    SELECT 0 AS [Level],PNLId , PNLParentId, PNLName
    FROM [dbo].[DimPNL]
    WHERE PNLParentId IS NULL 
    UNION ALL
    SELECT CTE.[Level] + 1 ,T1.PNLId,T1.PNLParentId,T1.PNLName 
    FROM [dbo].[DimPNL] AS T1
    INNER JOIN CTE
    ON T1.PNLParentId = CTE.PNLId
)
-- order the results into a #temp table
SELECT *
INTO ##temp
FROM CTE
ORDER BY level desc

DECLARE @PNLName As varchar(MAX)   

DECLARE db_cursor CURSOR FOR  
SELECT PNLName
FROM ##temp 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @PNLName 

WHILE @@FETCH_STATUS = 0   
BEGIN   
       INSERT INTO dbo.[test1]//from here I have a problem 
       Exec  dbo.[spGetResult] @PNLName

       FETCH NEXT FROM db_cursor INTO @PNLName 
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

I always get

stored procedure an insert exec statement cannot be nested

I have a look here But I didn't understand very much how to resolve the problem ?

Upvotes: 1

Views: 1929

Answers (1)

M.Stoop
M.Stoop

Reputation: 119

Your best bet is to rewrite the stored procedure to a table function. Performance-wise an inline function works best, but it's usually more difficult to functionally rewrite a procedure like this.

Most stored procs can easily be rewritten to a table valued function though.

Take a look here for more info: http://technet.microsoft.com/en-us/library/ms187650(v=sql.105).aspx

Once you've rewritten it, you can nest the insert into/select statements.

INSERT INTO [TABLE] (col1, col2, ...) SELECT tvfcol1, tvfcol2, ... from dbo.func_TVF_blabla(@param1, @param2, ...)

Upvotes: 1

Related Questions