Calling a stored procedure from another stored procedure

skbohler used Ask the Experts™

I have a SPROC1 which sends results back to the calling .asp program via a select statement like:

Select distinct MessageID, MessageDate from #Messages order by MessageDate desc;

I'd like to write an SPROC2 which executes SPROC1 and then can loop through records returned from SPROC1.

What would the syntax be to call SPROC1 and then loop through the results?

Thanks in advance.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer
Loops are not a natural way of processing data in RDBMS. Here sets are often the better alternative, cause faster.

Depening on your SPROC1 you can use

DECLARE @Messages TABLE ( MessageID INT , MessageDate DATE);


Open in new window

Then you can use a CURSOR for a loop.

btw, a sproc does not "send" results.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
You'll need to know the structure of the results coming back, because you'll have to create a table ahead of time to store them.

You can create the actual temp table to be used, or you can create your own version of the table and store them there.

Make sure the called proc only returns 1 result set.

Then you can do this:

CREATE TABLE #messages_2 (
    MessageID int NULL,
    MessageDate datetime NULL

INSERT INTO #messages_2
EXEC dbo.SPROC1 /*@... = ..., ... */ /*param(s), if any, for SPROC1*/

Then you can use a cursor to loop thru #messages_2, or you can use set-based processing on it.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

if proc2 'owns' the temp table and executes proc1 then proc 1 does have access to the table....

e.g. (for a demonstration only - would need more work)
create procedure usp_proc2
 create table #messages(MessageID int, MessageDate datetime);

 exec usp_proc1

  select * from #messages


create procedure usp_proc1
  insert #messages values (1,getdate())

-- now execute proc2 to see if proc1 populated

exec usp_proc2

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial