-2

I need to store the stored procedure results in a temp table. But the stored procedure result may be any table in any columns ordering.

Is there a general table (with not specified columns) that we can insert any table into it? I know I can use a function but is it possible using a stored procedure?

This is the code:

begin transaction

create table temp_table as

exec my_store_proc 1,111

select * from temp_table

drop table temp_table

commit transaction

in the code above, my_store_proc result is a table with 6 columns (for example)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ali motamedi
  • 103
  • 5
  • 2
    You may get better advice if you also explain WHY you are trying to do this. – John Sobolewski Oct 08 '12 at 11:32
  • i have a store proc that generates my program reports. now i want to use this result in another join query, but dont want to copy the code in a function nor change the base code (store proc) – ali motamedi Oct 08 '12 at 11:40
  • 1
    You *are* best moving the code into a function where possible. Then change the pre-existing stored proc to call that function (as a simple API style wrapper) to maintain backwards compatability. – MatBailie Oct 08 '12 at 11:44
  • no it is not possible! because our card reader (hardware) use this store procedure. therefore any change to it should be under permission of another programmer (he change his codes very hard). we prefer to think it as the final way. thanks – ali motamedi Oct 08 '12 at 11:52
  • Then you prefer to stick dogmatically to methods that constrain development, increase complexity, and reduce maintainability. You may have other reasons, but you are asking for advice and the advice is that your desired approach will cause more problems and scope for error, not less. – MatBailie Oct 08 '12 at 12:03

3 Answers3

2

You could use an approach like this.

declare @t table(ID int, Name nvarchar(max), AnotherId int)

insert @t
exec theStoredProc

-- then you can utilize the result in a join

select * from SomeTable inner join @t as t on SomeTable.ID =  t.ID....
John Sobolewski
  • 4,512
  • 1
  • 20
  • 26
  • This question has a great discussion of :How to SELECT * INTO [temp table] FROM [stored procedure]" http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure/1228165 – John Sobolewski Oct 08 '12 at 13:26
  • Note: it is going to be difficult to get around specifying the columns. Unless the sproc returns different results based on the input wouldn't the result set always have the same shape anyway? – John Sobolewski Oct 08 '12 at 18:27
1

Its better to go tiwth function only because with procecure its not possible. Create function which is having return type table and use that will work fine.

you can do this, but column sequence is not in control 

--INSERT...EXECUTE procedure example
 INSERT @temptable EXECUTE proceudrename
Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
-1

Try this

begin transaction

create table @tmp ( Your fields define in my_store_proc procedure )

insert into @tmp exec my_store_proc 1,111

select * from @tmp

commit transaction

Chirag
  • 324
  • 2
  • 4
  • 27