Menu
How can i execute a stored procedure in another stored procedure in SQL server? How will I pass the parameters of the second procedure.?
380
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.
In SQL Server Management Studio (SSMS), expand Programmability > Stored Procedures, right click a stored procedure and select Execute Stored Procedure. In the execute procedure page, enter the parameter @CustID value as 10 and click OK. It returns the following T-SQL statement with a variable @return_value.
If you are trying to call the procedure get_manager_details inside test_procedure then you first need to create the test procedure. Add create or replace procedure test_procedure . Then after creating the test_procedure you can execute it in an anonymous block which will call the get_manager_details procedure.
If you only want to perform some specific operations by your second SP and do not require values back from the SP then simply do:
Exec secondSPName @anyparams Else, if you need values returned by your second SP inside your first one, then create a temporary table variable with equal numbers of columns and with same definition of column return by second SP. Then you can get these values in first SP as:
Insert into @tep_table Exec secondSPName @anyparams Update:
To pass parameter to second sp, do this:
Declare @id ID_Column_datatype Set @id=(Select id from table_1 Where yourconditions) Exec secondSPName @id Update 2:
Suppose your second sp returns Id and Name where type of id is int and name is of varchar(64) type.
now, if you want to select these values in first sp then create a temporary table variable and insert values into it:
Declare @tep_table table ( Id int, Name varchar(64) ) Insert into @tep_table Exec secondSP Select * From @tep_table This will return you the values returned by second SP.
Hope, this clear all your doubts.
61
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
