How to Pass the variables from one Procedure to Another Procedure with OUTPUT Parameter
Here is the Example:
Step 1: Create a Table
CREATE TABLE StudentsDetails
(
[Studentid] [int] IDENTITY(1,1) NOT NULL,
[Firstname] [nvarchar](200) NOT NULL,
[Lastname] [nvarchar](200) NULL,
[Email] [nvarchar](100) NULL
)
Insert into StudentsDetails (Firstname, lastname, Email)
Values('Vagalla', 'Suresh', 'vagalla.suresh@gmail.com')
Insert into StudentsDetails (Firstname, lastname, Email)
Values('vagalla', 'Saran', 'vagalla.Saran@gmail.com')
Insert into StudentsDetails (Firstname, lastname, Email)
Values('Rajesh', 'Kumar', 'Raj.Kumar@gmail.com')
Insert into StudentsDetails (Firstname, lastname, Email)
Values('Prasad', 'Reddy', 'Prasad.reddy@gmail.com')
Insert into StudentsDetails (Firstname, lastname, Email)
Values('Sukanya', 'reddy', 'Sukanya.reddy@gmail.com')
select * from StudentsDetails
Step 2:
Declare the Parameters.
As of my concern, StudentID is the Input value and Expecting 3 output values.
create procedure GetStudentDetailsWithID
(
@studentid INT, --Input parameter , Studentid
@studentFirstName VARCHAR (200) OUTPUT, -- Output parameter of student Firsst name
@studentLastName VARCHAR (200) OUTPUT, -- Output parameter of student Last name
@StudentEmail VARCHAR (200) OUTPUT -- Output Parameter of student email Address
)
AS
/*=====================================================================================================
With one input value, getting full details of the Student
=====================================================================================================*/
BEGIN
SELECT @studentFirstName = Firstname,
@studentLastName = Lastname,
@StudentEmail = email
FROM StudentsDetails
WHERE StudentID = @studentid
END
Step 3:
Now I am trying to get the Final result
Create procedure ReturnFullData(@studentid int)
as
/*=====================================================================================================
With one input value, getting full details of the Student
=====================================================================================================*/
begin
Declare @studentFName as nvarchar(200) -- Declare a variable to get studentFirstName
Declare @studentLName as nvarchar(50) -- Declare a variable to get studentLastName
Declare @StudentEML as nvarchar(50) -- Declare a variable to get Studentemail
Execute GetStudentDetailsWithID 1 , @studentFirstName = @studentFName output, @studentLastName = @studentLName output, @StudentEmail = @StudentEML output
select @studentFName as FirstName,@studentLName as LastName ,@StudentEML as Email -- "Select" Statement is used to show the output from Procedure
end
-- here is your Fina output
exec ReturnFullData 1
