Consider the following Procdre in SQL SERVER
USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Zulqurnan Aslam
-- =============================================
ALTER PROCEDURE [dbo].[SpEmployeeIDSelection]
@ID int, @EmpName varchar(50) output
AS
BEGIN
SET NOCOUNT ON;
SELECT @EmpName=Emp_Name FROM [dbo].[TableName] WHERE [Emp_ID]=@ID;
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Zulqurnan Aslam
-- =============================================
ALTER PROCEDURE [dbo].[SpEmployeeIDSelection]
@ID int, @EmpName varchar(50) output
AS
BEGIN
SET NOCOUNT ON;
SELECT @EmpName=Emp_Name FROM [dbo].[TableName] WHERE [Emp_ID]=@ID;
END
This St. Procedure will give the name of the person against ID. ID will be passed as a parameter to it. There is another variable “EmpName” which will be used as an output variable. After executing the SELECT query “EmpName” will contain the name of the employee.
-- Second Procedure
USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Zulqurnan Aslam
-- =============================================
ALTER PROCEDURE [dbo].[Sp_Test_Insertion]
-- Add the parameters for the stored procedure here
@fname varchar(50) output,
@lname varchar(50)
AS
BEGIN
SET NOCOUNT ON;
EXEC SpEmployeeIDSelection 3,@fname output
INSERT INTO [dbo].[TableName](
[FirstName],
[LastName]
)
VALUES(
@fname,
@lname
)
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Zulqurnan Aslam
-- =============================================
ALTER PROCEDURE [dbo].[Sp_Test_Insertion]
-- Add the parameters for the stored procedure here
@fname varchar(50) output,
@lname varchar(50)
AS
BEGIN
SET NOCOUNT ON;
EXEC SpEmployeeIDSelection 3,@fname output
INSERT INTO [dbo].[TableName](
[FirstName],
[LastName]
)
VALUES(
@fname,
@lname
)
END
Now here are two variables. @fname and @lname.
This Procedure will call the first procedure Sp_EmployeeID_Selection. And insert the first name and last name. It will get first name from the First Procedure.
Look at this line
EXEC Sp_EmployeeID_Selection 3,@fname output
This line is calling first procedure. It is passing 2 parameters(3,@fname).
3 is the ID against which we want employee name and @fname is the output variable where the result will be stored. The Emp name returned by the first procedure will be stored in @fname.
