STORE PROCEDURE OUTPUT PARAMETERS

SQL STORE PROCEDURE OUTPUT

Output parameters in a stored procedure allow you to return values from the procedure.

When creating the stored procedure, you define an output parameter using the OUTPUT keyword. This parameter will hold the value that you want to return.

Let’s understand the store procedure with an example. For demonstration purposes, we will be using the table emp.

Table EMP

Table Project

Example:

Objective: In this example, we will use output parameters in the store procedure.

CREATE PROCEDURE SP_PR_OUT(@Dept as varchar(20) , @EmployeeCount as int OUTPUT) As Begin

SELECT @EmployeeCount = COUNT(*) FROM Tbl_Emp WHERE Department = @dept

End

Execute output store procedure:

Output:

DECLARE @Count INT;
EXEC SP_PR_OUT @dept = ‘IT’ , @EmployeeCount = @Count OUTPUT
Print @Count