Execute a SQL Server Stored Procedure from another Stored Procedure

Question

Just wondering what the syntax is to Execute a SQL Server Stored Procedure from another SQL Server Stored Procedure.

As well...are there any generally accepted SQL Server Stored Procedure Statements...Like BEGIN...END...etc.. that we should invoke as a standard in our SQL Server Stored Procedures?

Answer 1

Hi anonymous user

what the syntax is to Execute a SQL Server Stored Procedure from another SQL Server Stored Procedure.

If you only want to execute another SP and do not require values back from the SP, then simply do:

Exec Another_SPName  @anyparameters  

If you need values returned by another SP inside your first SP, then you could create a temporary table variable with equal numbers of columns and with same definition of columns return by another SP. Then you can get these values in first SP.
Check this example: Suppose the another SP returns Id and Name where type of id is int and name is of varchar(64) type.

CREATE PROCEDURE First_SP  
AS  
BEGIN   
  DECLARE @temp_table TABLE  
   (   
     Id int,  
     Name varchar(64)   
   )  
  INSERT INTO @temp_table  
  EXEC SecondSP  
  SELECT * FROM @temp_table  
END  

If you want to pass parameter from another SP to first SP, then you need OUTPUT . Here's an example of how you can pass a mutable parameter from one proc to another:

CREATE PROCEDURE InnerProcedure  
@user_id INT,   
@hcp_id INT OUTPUT  
AS   
BEGIN  
    IF @user_id = 1  SET @hcp_id = 100  
    ELSE SET @hcp_id = 200  
END  
GO  

CREATE PROCEDURE OuterProcedure  
@user_id INT  
AS  
BEGIN  
    DECLARE @hcp_id INT  
    EXEC InnerProcedure @user_id, @hcp_id OUTPUT  
    SELECT @hcp_id  
END  
GO  

EXEC OuterProcedure 1  
EXEC OuterProcedure 2  

are there any generally accepted SQL Server Stored Procedure Statements...Like BEGIN...END

Create procedure syntax specifies BEGIN / END as optional. You can choose to add them or not in your SP (without any IF, WHILE blocks...).

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

As PS says, it is no different from calling a stored procedure directly. If you have any problems, please be more specific.

PS makes a good point about TRY-CATCH. Error-handling is a good thing, or else you can run into nasty surprises when there is an unexpected error. I have a primer on error handling here: https://www.sommarskog.se/error_handling/Part1.html.

Answer 3

simply call another proc - EXECUTE procname.

Thank you!