Calling a stored procedure from another stored procedure

skbohler
skbohler used Ask the Experts™
on
Hello,

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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer
Commented:
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);

INSERT INTO @Messages
    EXECUTE SPROC1;

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
Commented:
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

Commented:
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
as
begin
 create table #messages(MessageID int, MessageDate datetime);

 exec usp_proc1

  select * from #messages

end
go

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

-- 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