Executing multiple stored procs from a stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Executing multiple stored procs from a stored proc

Author

Topic

dcummiskey
Starting Member

26 Posts

Posted - 2004-07-28 : 16:38:46

Hello -
IF i have a stored procedure that executes several other procedures..do they execute in the order listed?

For instance in my proc i have:

exec storeproc1

exec storedproc2

exec storedproc3

Will it complete processing storedproc1 before it moves onto storeproc2?

Also, is there a check in place to verify it executed? should I return a value from storedproc1 ... check that value and if its true then execute storedproc2..guess I'm looking for an example on how to execute multiple stored procedures from a stored procedure.

thanks,
Dan

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-28 : 17:27:24

Yes, stored procedures make this pretty easy, although very few folks make use of those features any more. As a short example:

CREATE PROCEDURE pA AS
BEGIN
   RETURN 0
END
GO

CREATE PROCEDURE pB AS
BEGIN
   RETURN 0
END
GO

CREATE PROCEDURE pC AS
BEGIN
   RETURN 0
END
GO

CREATE PROCEDURE pMaster AS
BEGIN
DECLARE @iRC INT

EXECUTE @iRC = pA
IF 0 = @iRC EXECUTE @iRC = pB
IF 0 = @iRC EXECUTE @iRC = pC

RETURN @iRC

If any of the procedures returns a non-zero value, the rest will not be called. Short, simple, and to the point!

-PatP

dcummiskey
Starting Member

26 Posts

Posted - 2004-07-29 : 10:24:08

sweet! thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources