call a stored procedure within a stored procedure

Question

call a stored procedure within a stored procedure

Marcus Farrugia

SSCarpal Tunnel

Points: 4718
More actions
October 20, 2014 at 10:34 am

#291180

What is the best way or is it possible to call a stored procedure within another stored procedure ...

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 1

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

Xedni SSCertifiable Points: 6951 More actions · Answer 2

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.

Executive Junior Cowboy Developer, Esq.[/url]

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 3

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

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 4

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.

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 5

Thank you all for your valuable comments and information.

call a stored procedure within a stored procedure

Cookies on SQLServerCentral