October 20, 2014 at 10:34 am
What is the best way or is it possible to call a stored procedure within another stored procedure ...
October 20, 2014 at 11:01 am
Marcus Farrugia (10/20/2014)
What is the best way or is it possible to call a stored procedure within another stored procedure ...
Quick demonstration
😎
USE tempdb;
GO
CREATE PROCEDURE dbo.PROC_ONE
(
@PARAM INT
)
AS
SELECT @PARAM * 2 AS RESULTS
GO
CREATE PROCEDURE dbo.PROC_TWO
(
@PARAM INT
)
AS
SET @PARAM = @PARAM * 3;
EXEC dbo.PROC_ONE @PARAM;
GO
EXEC dbo.PROC_TWO 7;
DROP PROCEDURE dbo.PROC_ONE;
DROP PROCEDURE dbo.PROC_TWO;
Result
RESULTS
42
October 20, 2014 at 2:07 pm
There are probably lots of schools of thought on this. IMHO, I try to avoid nesting procedure calls, primarily because it can be difficult to troubleshoot. I have fond memories of calling a procedure which basically calls one of 8 other procedures depending on inputs, each of which can call multiple procedures. Trying to set up sample calls to these procs and follow them from start to finish can be a nightmare. The same can be said of views, functions and triggers (all of which have their place as well).
To answer your question simply, yes, you can call a procedure within a procedure just as Erikur described. I would suggest trying to nest proc calls only a level deep to encapsulate logic you want to use across multiple procedures. I'm not arguing against nested proc calls altogether, but give it a moment and decide if nesting a procedure call is going to make your life easier or harder in the long run.
October 20, 2014 at 3:45 pm
One very good reason for calling procedures from within a wrapper procedure is when you're dealing with IF statements in the procedure that would lead to bad parameter sniffing issues since all the statements within a procedure are compiled at the same time, but not external procedures. Having secondary procedures is a great way around this issue. But, I agree, nesting them multiple layers deep is usually a recipe for disaster.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2014 at 8:45 pm
Another important aspect is the use of SQL code modules as a means for code modularisation, nudging the SDLC more towards SOLID so to speak, something that is immensely beneficial to any TDD environment. Obviously a stored procedure as a unit/module/interface/what ever has then to call another regardless of the level/depth.
😎
October 21, 2014 at 7:30 am
Thank you all for your valuable comments and information.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply