Run stored procedure within another stored procedure

[schum1]

Hello,

Our company has an Intranet that displays contact details for contact persons and organisations on separate pages for each. The asp pages get their information from SQL stored procedures (GetContactPersonAddresses for contact persons, and GetContactOrgAddresses for contact organisations).

Contact persons are linked to an organisation, and I want to display the organisation's address if the person does not have one. Can this be done by running GetContactOrgAddresses within the ContactPersonAddresses?

I am a novice (a contractor designed the ASP pages and stored procedures) Any ideas are very welcome.

Thank you
Marietjie

 

[hmckillop]

yes you can call a proc from within a proc.
You can use the IF EXISTS statement to check if the first query returns a resultset and then exec the other stored proc inside your original one.

 

[nigelrivett]

Yes but you would probably be better including the processing in the SP.

You can call another SP and get the result by either

-- output parameter
declare @result ...
exec mysp @p1, ..., @result out

-- resultset
create table #result (...)
insert #result
exec mysp @p1, ...


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[schum1]

Thank you, both of you, for replying so quickly. I'll try out your suggestions