May 16, 2017 at 3:43 am
I was wondering if anyone can offer any insight on this:
I have four stored procs:
proc 1
proc 2
proc 3
proc 4
Now what I would like to do execute these in order, i.e. proc1,2 ...and so on.
I imagine the way to do this is to create a master procedure but how I am abit clueless as to how to execute these procs in order.
I found this code online but Im not sure its what im looking for- nor do I fully understand how it works.
Any explanation/help will be greatly appreciated
thanks in advance
USE WORK
GO
CREATE PROC PROC1
AS PRINT'1' RETURN 0
GO
CREATE PROC PROC2
AS PRINT'2' RETURN 1
GO
CREATE PROC PR0C3
AS PRINT'3' RETURN 0
GO
IF OBJECT_ID('dbo.exhibit_testproc_edu') is null exec('create procedure dbo.exhibit_testproc_edu as return(0)')
GO
ALTER PROCEDURE [dbo].[usp_exhibit_testproc_edu]
AS
DECLARE @result INT
DECLARE @sql NVARCHAR (MAX)= N''
DECLARE @TBL1 TABLE (
[STEP] INT,
[PNAME] NVARCHAR(513))
INSERT INTO @TBL1 ([STEP],[PNAME])
VALUES
(1,N'PROC1'),
(2,N'PROC2'),
(3,N'PROC3')
select @sql= @sql + 'exec @result = '+ QUOTENAME(PNAME) + ' if @result <> 0 return;'
from @tbl1 order by step
exec sp_executesql @sql, N'@result int output', @result output
if @result <> 0
begin
print 'Hello, Hi'
end
GO
EXEC [dbo].[usp_exhibit_testproc_edu]
May 16, 2017 at 3:58 am
SQL already runs top down already, so I'm not sure what your point is here. If you have 3 SP's, each one will be executed in a top down order. For example:EXEC MySP1;
EXEC MySP2;
EXEC MySP3;
This will execute MySP1 first, then MySP2, and finally MySP3 in that order.
So, if you want an SP to run several other SP's, in a specific order, then it would be as "simple" as:USE DevTestDB;
GO
CREATE PROC MySP1 @int int AS
PRINT @int;
GO
CREATE PROC MySP2 @int int AS
PRINT @int + 1;
GO
CREATE PROC MySP3 @int int AS
PRINT @int + 2;
GO
CREATE PROC AllMySPs @Int int AS
PRINT 'Executing SP1...';
EXEC MySP1 @Int;
PRINT 'Executing SP2...';
EXEC MySP2 @Int;
PRINT 'Executing SP3...';
EXEC MySP3 @Int;
GO
EXEC AllMySPs 1;
GO
DROP PROC AllMySPs;
DROP PROC MySP1;
DROP PROC MySP2;
DROP PROC MySP3;
GO
This prints the following:Executing SP1...
1
Executing SP2...
2
Executing SP3...
3
On the other hand, when expecting results in a specific order from a dataset, the only way to guarantee it is with an ORDER BY, regardless of the order you inserted the data into the table, or the order on your clustered index.
In your code above, you are using Dynamic SQL. Does this imply that the SPs might might be running are dynamic? If so, what you have there needs to be further considered, as it could easily have SQL injected into it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 16, 2017 at 8:55 am
thanks for you comprehensive reply.
This should solve my conundrum
thanks again!
June 22, 2021 at 10:35 am
Wow. Can't believe I found a post where the developer's requirement is so close to mine. I wish to do exactly the same as ExhibitA. But . . what concerns me is what happens if there's an error encountered while running SP1. I suppose that'll break the flow and stop the execution of the master Stored Procedure (consequently, SP2 & SP3 are never executed). What wish to do is make sure that ALL the EXEC statements should attempt to run in that master procedure (it's OK if some of them fail but they should at least be run). In my case, I have used the usual TRY...CATCH construct for error handling in Master Stored Procedure. But, again, coming back to my original question --> How do I make sure that ALL the SPs are at least attempted to run sequentially ? Thanks in advance.
Best Regards,
Nachiket
June 22, 2021 at 10:44 am
But, again, coming back to my original question --> How do I make sure that ALL the SPs are at least attempted to run sequentially ? Thanks in advance.
What I said in my answer back in 2017 hasn't change. SQL will run "top down", so each call to your procedures will be in the order you define it in. SQL dosen't "skip" rows unless you have things like GOTO or IF...ELSE statements that control the logical flow of the statements.
CREATE PROC AllMySPs @Int int AS
PRINT 'Executing SP1...';
EXEC MySP1 @Int;
PRINT 'Executing SP2...';
EXEC MySP2 @Int;
PRINT 'Executing SP3...';
EXEC MySP3 @Int;
GO
For the above, SP1 will always be run first, and SP3 will always be run last; they will never be executed in a different order.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 22, 2021 at 10:51 am
Hi Thom A, sorry if did not make my reply clear. My question is : While running AllMySPs, if SP1 encounters some error and fails, then the following statements like EXEC MySP2 @int, EXEC MySP3 @ int will not get executed, right ? Because the execution flow of AllMySPs will be broken. Am I right to assume this ? Or will the master SP continue to execute remainder of the lines even after running into an error at EXEC MySP1 ?? A bit confused here.
Many thanks for responding. ??
June 22, 2021 at 11:24 am
Hi Thom A, sorry if did not make my reply clear. My question is : While running AllMySPs, if SP1 encounters some error and fails, then the following statements like EXEC MySP2 @int, EXEC MySP3 @ int will not get executed, right ? Because the execution flow of AllMySPs will be broken. Am I right to assume this ? Or will the master SP continue to execute remainder of the lines even after running into an error at EXEC MySP1 ?? A bit confused here.
Many thanks for responding. ??
If you want to continue on in the event the procedure you are calling errors, then yes, a TRY...CATCH is exactly what you want. In very simple terms, using my examples for 4 years ago:
CREATE PROC MySP1 @int int AS
PRINT @int / 0; --To error
GO
CREATE PROC MySP2 @int int AS
PRINT @int + 1;
GO
CREATE PROC MySP3 @int int AS
PRINT @int + 2;
GO
CREATE PROC AllMySPs @Int int AS
PRINT 'Executing SP1...';
BEGIN TRY
EXEC MySP1 @Int;
END TRY
BEGIN CATCH
PRINT 'MySP1 failed';
END CATCH
PRINT 'Executing SP2...';
BEGIN TRY
EXEC MySP2 @Int;
END TRY
BEGIN CATCH
PRINT 'MySP2 failed';
END CATCH
PRINT 'Executing SP3...';
BEGIN TRY
EXEC MySP3 @Int;
END TRY
BEGIN CATCH
PRINT 'MySP3 failed';
END CATCH
GO
EXEC AllMySPs 1;
GO
DROP PROC AllMySPs;
DROP PROC MySP1;
DROP PROC MySP2;
DROP PROC MySP3;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 22, 2021 at 3:50 pm
Yep. That answers my question. Many thanks Thomas.
Regards,
Nachiket
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply