1

I'm trying to call a sproc from within another sproc and putting the results in a temp table without first creating the temp table. Is it possible to do this, or is there a better way? I want to use the sprocB or functionB whose result set has multiple columns and multiple rows. thx.

sproc A  
..
begin
  -- create table #tmp.... -- Try not to create the #tmp table first if possible
  exec sproc_B ... put results from sproc_B in #tmp

end

sproc B
..
@id int
..
begin
  select table from aTable where id = @id
end

Similiar question was asked here.

Community
  • 1
  • 1
RetroCoder
  • 2,597
  • 10
  • 52
  • 81
  • 1
    possible duplicate of [How to SELECT * INTO \[temp table\] FROM \[Stored Procedure\]](http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure) – John Sansom Jan 16 '12 at 15:18
  • But the other question does not mention calling from one sproc to another sproc. – RetroCoder Jan 16 '12 at 15:31

3 Answers3

4
  create table #tmp....

  insert  #tmp
  exec sproc_B
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Is it possible to add it into a temp table without first creating the temp table? – RetroCoder Jan 16 '12 at 15:13
  • 1
    @RetroCode: No. The alternative would be to use the dreaded table UDF. – John Sansom Jan 16 '12 at 15:15
  • 1
    @RetroCoder: no. INSERT .. EXEC requires a pre-existing table – gbn Jan 16 '12 at 15:16
  • 1
    @JohnSansom: if an in-line TVF, it's just a in-line macro. I assume you mean a multi-statement TVF? – gbn Jan 16 '12 at 15:16
  • 1
    @gbn: Indeed sir. One would question the value of having a single statement stored procedure in the problem context. If it were so, the entire issue that is trying to be worked around would be avoidable simply by bringing the relevant code up to the calling procedure. – John Sansom Jan 16 '12 at 15:24
2
CREATE TABLE #tmpTable
(
   COL1 INT,
   COL2 INT   
)

INSERT INTO #tmpTable 
Exec spGetResultset 'Params'
John Sansom
  • 41,005
  • 9
  • 72
  • 84
1

Yes, but you have to create table prior to using in. The syntax is:

INSERT INTO YourTable EXEC YourProc

No need to say, that structure of the table should match the SP's output?

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54