Calling a stored procedure from another stored procedure

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.
skbohlerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
0
Scott PletcherSenior DBACommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.