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
