Using Output Parameters in SQL Server Stored Procedures

The legacy version of this page correctly identified that SQL Server supports both a RETURN value and output parameters, but it blurred two distinct ideas: (1) returning a status code versus (2) returning business data. It also showed an example where @EmployeeID was marked as OUTPUT, which is not a realistic design because @EmployeeID is typically an input value used to locate a row. This rewrite clarifies best practices for SQL Server 2022: use RETURN for a single integer status code, and use OUTPUT parameters to return one or more values (of any datatype) back to the caller.

SQL Server provides two common mechanisms to send information back to the calling program:

1. Return value (RETURN): returns a single integer that typically represents success (0) or failure (non-zero). This is best for status codes.
2. Output parameters (OUTPUT): parameters that the procedure can set and return to the caller. Output parameters can be almost any SQL Server datatype and you can return multiple values in a single call.

Use output parameters when the caller needs specific values computed or retrieved by the procedure, such as:

• A single column value (e.g., last name, total amount, count of rows affected).
• Multiple related values (e.g., first name + last name + department).
• A generated identifier (e.g., a newly created employee ID) alongside a status code.

The following example accepts an employee ID as an input parameter and returns the employee’s last name through an output parameter:

CREATE OR ALTER PROCEDURE dbo.usp_GetLastName
    @EmployeeID int,
    @LastName   nvarchar(50) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @LastName = e.LastName
    FROM dbo.Employees AS e
    WHERE e.EmployeeID = @EmployeeID;
END;

Notice the pattern: the input parameter (@EmployeeID) identifies the row, and the output parameter (@LastName) carries a value back to the caller.

When you call a stored procedure that has output parameters, you must declare variables in the calling scope and pass them with the OUTPUT keyword:

DECLARE @LastName nvarchar(50);

EXEC dbo.usp_GetLastName
     @EmployeeID = 42,
     @LastName   = @LastName OUTPUT;

SELECT @LastName AS LastName;

A stored procedure can return multiple values by defining multiple output parameters. This is useful when you want to return several scalar values without returning a full result set.

CREATE OR ALTER PROCEDURE dbo.usp_GetEmployeeSummary
    @EmployeeID int,
    @FirstName  nvarchar(50) OUTPUT,
    @LastName   nvarchar(50) OUTPUT,
    @DeptName   nvarchar(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        @FirstName = e.FirstName,
        @LastName  = e.LastName,
        @DeptName  = d.DepartmentName
    FROM dbo.Employees   AS e
    LEFT JOIN dbo.Department AS d
        ON d.DepartmentID = e.DepartmentID
    WHERE e.EmployeeID = @EmployeeID;
END;

A common design is to combine both approaches: return a status code with RETURN, and return business data via output parameters. Keep the responsibilities clear:

• RETURN = status (single integer).
• OUTPUT parameters = values the caller needs.

Click the Exercise link below to practice creating stored procedures with output parameters.

Using Output Parameters - Exercise