CODEWITHSUNDEEP
sql-servert-sqlssms
PaulP
PaulP

Reputation: 105

SQL Server Management Studio - run multiple stored procedures in series

I would like to test 30 stored procedures against various input to see if any produce errors. The stored procedure share the same parameters. When I run all of them (even just several of them) in SSMS, the execution runs and never completes. It seems that maybe they are running in parallel and bogging down the server. How can I instead execute the stored process in series without manually executing one at a time?

DECLARE @spResult int;
DECLARE @paramA int;
DECLARE @paramB int;
DECLARE @paramC int;

set @paramA = 2013
set @paramB = 1;
set @paramC = 10;

exec @spResult = rstoredProc1    @paramA ,@paramB  ,@paramC
exec @spResult = rstoredProc2    @paramA ,@paramB  ,@paramC
exec @spResult = rstoredProc3    @paramA ,@paramB  ,@paramC
exec @spResult = rstoredProc4    @paramA ,@paramB  ,@paramC
exec @spResult = rstoredProc5    @paramA ,@paramB  ,@paramC
...

Upvotes: 2

Views: 5122

Answers (1)

X3074861X
X3074861X

Reputation: 3819

There are many ways to go about this. Personally, I use an IF to keep the iteration going, and RETURN if I don't get the success code back. In your case you could do something like :

EXEC @spResult = rstoredProc1 @paramA ,@paramB ,@paramC
IF @spResult <> 0
     RETURN
ELSE
     PRINT 'rstoredProc1 executed successfully'

EXEC @spResult = rstoredProc2 @paramA ,@paramB ,@paramC
IF @spResult <> 0
     RETURN
ELSE
     PRINT 'rstoredProc2 executed successfully'

Upvotes: 2

Related Questions