Execute Stored Procedure from Another Stored Procedure in SQL Server

Introduction:
This article explains how to call or execute one stored procedure from another stored procedure in sql server or execute stored procedure with parameters from another stored procedure in sql server. To call or execute stored procedure within another stored procedure we need to create stored procedure in sql server and call that procedure like “EXEC SPSAMPLE1” from another stored procedure.

Create First Stored Procedure 

 --- First Stored Procedure
CREATE PROCEDURE SPSAMPLE1
@tempid INT,
@tempname varchar(50)
AS
BEGIN
DECLARE @temp1 TABLE (Id INT, Name VARCHAR(50), Location VARCHAR(50))
INSERT INTO @temp1 (Id, Name, Location)
VALUES(1,'Tom','UK'),
(2,'Shaim','US'),
(3,'Jack','Dubai')
SELECT * FROM @temp1 WHERE Id=@tempid and Name=@tempname
END

Create Second Stored Procedure to execute first procedure

 --- Second Stored Procedure
CREATE PROCEDURE SPSAMPLE2
@id INT,
@name varchar(50)
AS
BEGIN
-- Calling First Procedure From Second Procedure
EXEC SPSAMPLE1 @tempid=@id, @tempname=@name
END

If you observe above query we are calling first procedure using “EXEC SPSAMPLE1 @tempid=@id, @tempname=@name” and sending parameters to first stored procedure using @tempid, @tempname. Here we need to use same parameter names whatever we mentioned in first procedure to send values otherwise it will throw error.

Now execute second procedure using following query it will automatically execute or call first procedure and return result

 -- Execute Second Procedure By Passing Paramters
EXEC SPSAMPLE2 @id=1, @name='Tom'

Output: