SQL Server : Insert stored procedure results into table based on parameters

sql-server-2008

I have a stored procedure Test_Sp which returns data in this way:

 Id  Name     Age   Address  State  Country
 1   ManiS    25     aaaa     bbb     ccc

This stored procedure is returning 6 columns of data, but I want to insert only the first 2 columns into a temp table..

My temp table variable is:

Declare @testTbl Table (RowId int identity, Name nvarchar(100), Age int);
INSERT INTO @testTbl(Name,Age) EXEC [Test_Sp] 23;
Select * from @testTbl;

But I get this error:

Msg 50000, Level 16, State 0, Procedure Test_Sp, Line 16
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I am aware about Select * into & if I create a temp table with same columns as stored procedure output means it will work..

My question: is it possible to insert just two columns into a temp table variable from the stored procedure output based on parameters?

Best Answer

Option 1:

Create an intermediate temp table with all the columns that sp returns and then do this:

INSERT INTO Temp
Exec [Test_Sp] 23;

Then 

INSERT INTO @testTbl(Name,Age)
select name,age from temp

Option 2:

Modify your sproc and add one more bit datatype parameter @limitedcolumn If @limitedcolumn=true the return only required columns else return all columns

INSERT INTO @testTbl(Name,Age) EXEC [Test_Sp] 23,true;
Related Topic