SQL Server Stored Procedures: Output Parameters Explained

What Are Output Parameters?

Output parameters are special parameters in stored procedures that allow the procedure to return values to the caller.

Unlike input parameters, which only send data into the procedure, output parameters send data back out.

• Declared with the OUTPUT keyword in the procedure definition.
• Can return single values such as status codes or computed results.
• Useful when you want to return multiple values without using result sets.

Declaring Output Parameters in Stored Procedures

To declare an output parameter, add the OUTPUT keyword after the parameter type in the procedure definition.

For example, to declare an integer output parameter named @TotalCount:

• CREATE PROCEDURE procedure_name
• @InputParam INT,
• @TotalCount INT OUTPUT
• AS
• BEGIN
• -- Procedure logic here
• END

Using Output Parameters in Procedure Logic

Inside the stored procedure, assign values to the output parameters just like regular variables.

These values will be available to the caller after the procedure executes.

• You can set output parameters based on queries, calculations, or status checks.
• Example: SET @TotalCount = (SELECT COUNT(*) FROM SomeTable WHERE Condition = @InputParam)

Calling Stored Procedures with Output Parameters

When calling a stored procedure with output parameters, you must declare a variable to receive the output value.

Use the OUTPUT keyword in the EXEC statement to indicate which parameters are output.

• DECLARE @ResultCount INT;
• EXEC procedure_name @InputParam = 5, @TotalCount = @ResultCount OUTPUT;
• SELECT @ResultCount AS TotalCount;

Example: Stored Procedure with Output Parameter

Here is a complete example demonstrating a stored procedure that returns the count of employees in a department using an output parameter.