CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2012
mano
mano

Reputation: 308

Unable to Insert data into temporary table from Stored procedure

I have been trying to fetch the data from a store procedure(GetData) insert into a temporary table and then filter. But could not get this working and getting a error message

An INSERT EXEC statement cannot be nested.

The store procedure(GetData) has got a stored procedure called within it.

I tried to create a function with the store procedure script but I am unable to do that and getting a error message

Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

Thus have been trying to find out a solution where I can get this working. Other store procedure cannot be modified as used in almost many places in the program.

By the way I wont be able to share the stored procedure script.

DECLARE @FromDate datetime
DECLARE @ToDate datetime
DECLARE @List VARCHAR(Max)
DECLARE @Filter VARCHAR(Max)

SET @FromDate = '1 July 2016 ';
SET @ToDate = '30 December 2017';
SET @List = '1,2,3,4';
SET @Filter= 'Manager, Staff'


DECLARE @Temp_Stage_Measurement TABLE
 (
 ClientID int,
 Status varchar(255),
 Declining varchar(255), Eligible varchar(255),
 NotEligible varchar(255), Reapplying varchar(255)
 )

INSERT @Temp_Measurement EXEC GetData @FromDate,@ToDate, @List  
--Get a error message "An INSERT EXEC statement cannot be nested." as GetData has another
--INSERT EXEC which inturn calls other store procedure which cannot be modified as used in almost many places in the program

SELECT * FROM @Temp_Measurement 
 WHERE @Filter is null or Reason IN (@Filter)

Even tried replacing following statement: INSERT @Temp_Measurement EXEC GetData @FromDate,@ToDate, @List

With SELECT * INTO #Temp_Measurement EXEC GetData @FromDate,@ToDate, @List

then getting the following error message

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Upvotes: 2

Views: 1512

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

I am sure you have Insert.. EXEC statement in GetData procedure. Insert.. EXEC cannot be nested that is a restriction in Sql Server.

Check the below article for various methods to share data between procedures by Erland Sommarskog

How to Share Data between Stored Procedures

Upvotes: 2

Related Questions