"Select" from Stored Procedures??

[dBjason]

SQL Server 7

I have a stored procedure that returns a recordset. Works great.

What I want to do is call that procedure from another stored procedure, and throw the data into a table. Something like:

CREATE PROC AS

SELECT Field1, Field2 INTO NewTable FROM MyStoredProc 'Parameter' ORDER BY Field2

--....Other Processing

I think I found a way to do it way back when, but forgot. Anyone have any ideas?

Thanks in advance,
Jason

 

[SQLDenis]

insert INTO NewTable
exec MyStoredProc 'Parameter'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Create a temp table that has the same structure as the data being returned from the sproc, then use insert exec.

Insert Into #TempTable
Exec MyStoredProc 'Parameter'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

The simplest but not always practical way: create table in parent sproc, then do INSERT INTO table EXEC sproc.



------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[dBjason]

Awesome! Thanks Denis & George!!

 

[SQLSister]

Note that if you want temporary processing you must use a #temp table not a table varaible. YOu cannot insert the results of a stored proc into a table variable. Anybody know if this changed in 2005 version?

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jbenson001]

I just tried a Select INTO a table var and it didn't work, I assume the same for SPs

Jim

 

[jbenson001]

Scratch that.. in 2005 I did:

DECLARE @t table(resposnetypeid int)
insert into  @t
exec test

and it worked...