10

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Manikandan Sethuraju
  • 2,873
  • 10
  • 29
  • 48
  • 2
    Why not just insert all 6 columns and ignore the 4 you don't care about? Or write a new stored procedure? Or change the stored procedure so that it knows you may only want two columns in some cases? – Aaron Bertrand Sep 27 '12 at 12:48
  • Hi @AaronBertrand ... i used this sp(Test_Sp) for another scenario, so i will add new columns to this sp, so it's not affect to this temp table variable insertion (because this temp variable insertion is used by different sp)... – Manikandan Sethuraju Sep 27 '12 at 12:56
  • It sounds like you need different stored procedures to serve different purposes. Unless the stored procedure is written to do so, there is no way to say "hey, just return two columns this time, okay?" There are [tricks with OPENROWSET](http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure) but perhaps you should [give this a read](http://www.sommarskog.se/share_data.html). – Aaron Bertrand Sep 27 '12 at 13:00
  • Specifically: http://stackoverflow.com/a/653726/61305 – Aaron Bertrand Sep 27 '12 at 13:01

1 Answers1

19

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;
AnandPhadke
  • 13,160
  • 5
  • 26
  • 33