How do I Execute SQL Stored Procedures from within another Stored Procedure?

Question

Everytime I refresh my DB with a backup file. I have to run about 10 stored procs separately because the backup file does not contain them.

Is there a way to have one single sql script that refrences all these 10 stored procs and just run that ONE file compared to TEN?

Answer 1

If you're asking if you can write a SQL script that references files in the local file system, then no. However, you could create one script file that combines the creation scripts into separate batches, or use an external batch processing tool (like the SQL command line) to run these script files in a batch.

Answer 2

this doesn't check for any errors..

CREATE PROCEDURE RUN_ALL
AS 

SET NOCOUNT ON

EXEC YourProcedureA

EXEC YourProcedureB

EXEC YourProcedureC

RETURN 0
GO

Answer 3

What I do is create a batch file which executes all SQL scripts within a specific folder using isql command line calls. the batch file simple loops through all files in the folder and executes the script. This process has the advantage of also being able to generate an output file of the results of the script for reference purposes.