Post
Ask Question

Executing SQL Stored Procedure from Another Stored Procedure

In large database applications, it is common to call one stored procedure from another stored procedure. In this blog, I will explain how to execute a stored procedure within another stored procedure in SQL Server.

Let's start with creating a stored procedure.

Here's the query for creating our first procedure:.
  1. create procedure Sp_insert  
  2. (  
  3. @ID int,  
  4. @TempName varchar(max) 
  5. )
  6. as 
  7. begin  
  8. Declare @SampleTable Table(id int, Name varchar(max))  
  9. Insert into @SampleTable(id,Name)values(@ID,@TempName)  
  10. select*from @SampleTable  
  11. end

Here is my second stored procedure. 

  1. create procedure Sp_Call
  2. (
  3. @SID int,
  4. @Name varchar(max)
  5. )
  6. as
  7. begin
  8. exec Sp_insert @ID=@SID,@TempName=@Name
  9. end
  10. From t  
Your stored procedure will be based on your database. So, create two stored procedures where data of the second stored procedure can be used in the first stored procedure. If you're new to stored procedures, please read, Learn Everything About Stored Procedures In SQL Server. 
 
In the above query, you may notice that we are calling the first stored procedure Sp_insert  and passing the parameter @ID and @TempName to it.

Now execute the second procedure using the following query. It will call the first procedure and return the result. 
  1. Exec Sp_Call @SID=1,@Name='Arun'  
The result:
 
   
Next: Creating and Using Stored Procedure In SQL Server