Output parameters in the stored procedure are used to return data back to the calling procedure or batch.

We will use the below table and data to understand the concept of Output parameters in the stored procedure with an example 

CREATE TABLE TblStudent
(
    SRNo VARCHAR(15) NOT NULL PRIMARY KEY,
    StudentName VARCHAR(50),
    FatherName VARCHAR(50),
	Gender VARCHAR(10),
    CourseCode VARCHAR(15)
)

GO

INSERT INTO TblStudent(SRNo, StudentName, FatherName,Gender, CourseCode) 
VALUES ('SR01','Kamal','Harish','Male','BCA'),
('SR02','Sachin','Gagan','Male','BBA'),
('SR03','Kapil','Raj','Male','MCA'),
('SR04','Tanuj','Sanjeev','Male','BPharma'),
('SR05','Puja','Shyam','Female','B.Arch')
We use OUT or OUTPUT keywords to declare an out parameter in a stored procedure.
Let's create a stored procedure with an output parameter to count the number of students by gender.

CREATE PROCEDURE GetStudentCountByGender
	@Gender NVARCHAR(10),
	@StudentCount int Output
AS
BEGIN
	SELECT @StudentCount = COUNT(SRNo) 
	FROM TblStudent 
	WHERE Gender = @Gender
END
This stored procedure returns a single OUT parameter (StudentCount), based on the specified input parameter (Gender). Output parameter (StudentCount) will return the total number of students based on the Gender from the TblStudent table.

Use the below steps to execute this stored procedure with OUTPUT parameter.

1. First, declare a variable of the same datatype as the type of output parameter. Here, we have declared @TotalStudents as an integer variable. 
2. Then pass the @TotalStudents variable to the stored procedure. You have to specify the OUTPUT keyword with the output parameter otherwise the variable will be NULL. 
3. Then execute the stored procedure as given below.
  
DECLARE @TotalStudents INT
EXEC GetStudentCountByGender 'Male',@TotalStudents OUT
SELECT @TotalStudents as TotalStudents
Output
When you execute a stored procedure with output-parameter then you should always specify output-parameter with output keyword otherwise you will get a null value.

In the following example, we have not specified the output keyword with @TotalStudents so it will return NULL value.

DECLARE @TotalStudents INT
EXEC GetStudentCountByGender 'Male',@TotalStudents 
SELECT @TotalStudents as TotalStudents
Output
When you pass values to stored procedure parameters with Parameter Name-Value pair then parameters order doesn't matter. Like in the below example first we are passing value to an output parameter and then to the input parameter. But still, we will get the total number of male students without any errors.
     
DECLARE @TotalStudents INT
EXEC GetStudentCountByGender @StudentCount=@TotalStudents OUT,@Gender='Male'
SELECT @TotalStudents as TotalStudents
Output