Output Parameters
Output parameters allow a Stored Procedure to return a value back to the user or application after execution.
What are Output Parameters?
Output parameters are parameters that allow a Stored Procedure to return a value to the caller.
Unlike input parameters, which receive values from the caller, output parameters send values from the Stored Procedure back to the caller.
Output parameters are useful when a Stored Procedure needs to calculate or retrieve a value and return that value after execution.
Basic Syntax
CREATE PROCEDURE ProcedureName
@ParameterName DataType OUTPUT
AS
BEGIN
SET @ParameterName = Value;
END;
The OUTPUT keyword indicates that the parameter can return a value from the Stored Procedure.
Example: Return Student Name
Suppose we want to create a Stored Procedure that accepts a StudentId and returns the student's name through an output parameter.
CREATE PROCEDURE GetStudentName
@StudentId INT,
@StudentName VARCHAR(100) OUTPUT
AS
BEGIN
SELECT @StudentName = Name
FROM Students
WHERE StudentId = @StudentId;
END;
Executing a Procedure with Output Parameter
To receive the value returned by an output parameter, we first declare a variable and then pass that variable to the Stored Procedure using the OUTPUT keyword.
DECLARE @Name VARCHAR(100);
EXEC GetStudentName
@StudentId = 101,
@StudentName = @Name OUTPUT;
SELECT @Name AS StudentName;
The variable @Name receives the value returned by the @StudentName output parameter.
Output Parameter with INT
Output parameters can also return numeric values such as INT.
CREATE PROCEDURE GetStudentAge
@StudentId INT,
@Age INT OUTPUT
AS
BEGIN
SELECT @Age = Age
FROM Students
WHERE StudentId = @StudentId;
END;
Executing INT Output Parameter
DECLARE @StudentAge INT;
EXEC GetStudentAge
@StudentId = 101,
@Age = @StudentAge OUTPUT;
SELECT @StudentAge AS Age;
The returned age is stored in the @StudentAge variable.
Multiple Output Parameters
A Stored Procedure can have more than one output parameter.
CREATE PROCEDURE GetStudentDetails
@StudentId INT,
@StudentName VARCHAR(100) OUTPUT,
@City VARCHAR(50) OUTPUT,
@Age INT OUTPUT
AS
BEGIN
SELECT
@StudentName = Name,
@City = City,
@Age = Age
FROM Students
WHERE StudentId = @StudentId;
END;
Executing Multiple Output Parameters
DECLARE @Name VARCHAR(100);
DECLARE @City VARCHAR(50);
DECLARE @Age INT;
EXEC GetStudentDetails
@StudentId = 101,
@StudentName = @Name OUTPUT,
@City = @City OUTPUT,
@Age = @Age OUTPUT;
SELECT
@Name AS StudentName,
@City AS City,
@Age AS Age;
Output Parameter with COUNT()
Output parameters can also be used to return calculated values such as the number of students.
CREATE PROCEDURE GetStudentCount
@StudentCount INT OUTPUT
AS
BEGIN
SELECT @StudentCount = COUNT(*)
FROM Students;
END;
Executing COUNT Output Parameter
DECLARE @TotalStudents INT;
EXEC GetStudentCount
@StudentCount = @TotalStudents OUTPUT;
SELECT @TotalStudents AS TotalStudents;
The total number of students is returned through the @StudentCount output parameter.
Input and Output Parameters Together
A Stored Procedure can use input and output parameters together.
CREATE PROCEDURE GetStudentCity
@StudentId INT,
@City VARCHAR(50) OUTPUT
AS
BEGIN
SELECT @City = City
FROM Students
WHERE StudentId = @StudentId;
END;
Execute:
DECLARE @StudentCity VARCHAR(50);
EXEC GetStudentCity
@StudentId = 101,
@City = @StudentCity OUTPUT;
SELECT @StudentCity AS City;
Important Points
- Output parameters return values from a Stored Procedure.
- The OUTPUT keyword is required when defining an output parameter.
- The OUTPUT keyword is also required when passing the parameter during execution.
- Output parameters can use different data types such as INT, VARCHAR, DATE and DECIMAL.
- A Stored Procedure can have multiple output parameters.
- Input and output parameters can be used together.
- Output parameters are useful for returning calculated or retrieved values to the caller.
Summary
Output Parameters allow Stored Procedures to return values back to the caller. They are declared using the OUTPUT keyword and can return values such as student details, calculated values and counts. A Stored Procedure can have one or multiple output parameters and can also use input and output parameters together.