SQL Server: Entering stored procedure results into a table based on parameters

Question

I have a Test_Sp stored procedure that returns data as follows:

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

This stored procedure returns 6 columns of data, but I want to insert only the first 2 columns in the temporary table.

My temp table variable:

 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, Test_Sp Procedure, Line 16
Cannot use the ROLLBACK statement in an INSERT-EXEC statement.

I know about Select * into , and if I create a temporary table with the same columns as the output of the stored procedure, this will work.

My question is: is it possible to insert only two columns in the temp table variable from the output of the stored procedure based on parameters?

Answer 1

Option 1:

Create an intermediate temporary table with all the columns returned by sp, and then do the following:

 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 another parameter datatype bit @limitedcolumn If @limitedcolumn = true, only the required columns returned else return all the columns

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