How to Call a stored procedure from other procedure

Posted on : 03-01-2012 | By : Devi Prasad | In : Interview Questions, SQL Server Basics, SQLServerPedia Syndication

Share:

0


Following are the very basic questions on stored procedures.

Question: How can i call a stored procedure from another stored procedure?

Answer:
Use EXEC statement.
Following syntax to execute a stored procedure:

EXEC STOREDPROCEDURENAME

Replace STOREDPROCEDURENAME with the name of the procedure.

You can add this piece of code in another stored procedure where ever required.

If the procedure has parameters, then below is the syntax.

EXEC STOREDPROCEDURENAME @parameter1=value1,@parameter2=value2,..

Replace STOREDPROCEDURENAME with the name of the procedure, parameters with the name of parameters and values with values for the parameters.
If you have one procedure in schema1 which should call another procedure in schema2.

Following is the code that creates two stored procedures. One of the procedure calls the other procedure using EXEC.

CREATE PROCEDURE Procedure1 --Creating Procedure1 
AS
SELECT '1'
GO
 
CREATE PROCEDURE Procedure2 --Creating Procedure2 that calls Procedure1 
AS
EXEC Procedure1 --CALLING PROCEDURE Procedure1
GO

EXEC Procedure2 --Executing Procedure2 

Question: How can i call a stored procedure from another stored procedure, both of them are from different schemas?

Answer:
USE EXEC Schemaname.procedurename

EXEC SchemaName.ProcedureName

Following is the simulation and solution for this question.

CREATE SCHEMA Schema1 --Creating Schema1
 
GO
 
CREATE SCHEMA Schema2 --Creating Schema2
GO
 
CREATE PROCEDURE Schema1.Procedure1 --Creating Procedure1 in Schema1
AS
SELECT '1'
GO

--Creating Procedure2 in Schema2 to call Procedure1 in Schema1
CREATE PROCEDURE Schema2.Procedure2 
AS
EXEC Schema1.Procedure1 --CALLING SCHEMA1 PROCEDURE FROM SCHEMA2 PROCEDURE
GO

--Executing Procedure2 in Schema2
EXEC Schema2.Procedure2

Now if you execute:

EXEC Schema2.Procedure2

You can see that it is calling Schema1.Procedure1 and displaying 1 as the result.

(Visited 50 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment



Tags: , , , , , , , , , , , , , , ,