How to Execute Stored Procedures in SQL Server

The legacy text explains the basics of executing a stored procedure, but it relies on outdated tooling terminology (Query Analyzer) and contains an example that passes a literal value into an output parameter, which cannot work as written. This rewrite modernizes the lesson for SQL Server 2022 and focuses on practical execution patterns using SSMS and EXEC/EXECUTE, including correct handling of output parameters.

You can execute (run) stored procedures from:

• SQL Server Management Studio (SSMS): the most common interactive tool for running Transact-SQL.
• Your application code: C#, Java, Python, etc., using a database driver and parameter binding.
• Automations: SQL Agent jobs or scheduled tasks that call procedures on a schedule.

In Transact-SQL, execute a stored procedure with EXEC (short form) or EXECUTE:

EXEC dbo.StoredProcedureName;

If the procedure requires parameters, you must provide them when you execute it. You can pass values in two ways:

1) Positional parameters

Values are provided in the same order the parameters are defined in the procedure:

EXEC dbo.usp_GetLastName 101, @EmployeeStatus OUTPUT;

2) Named parameters (recommended)

Named parameters are clearer and safer, especially when procedures have multiple parameters:

EXEC dbo.usp_GetLastName
     @EmployeeID = 101,
     @EmployeeStatus = @EmployeeStatus OUTPUT;

Named parameters reduce mistakes because the parameter name documents your intent and you are not dependent on the original parameter order.

Rules for output parameters

1. The calling code must include the OUTPUT keyword when supplying an output parameter.
2. You must pass a variable to receive the output value (not a literal constant).
3. The variable type should match (or be compatible with) the output parameter type.

Throughout this module you have created and modified usp_GetLastName. Here is a concise recap of what the procedure is intended to do.

CREATE OR ALTER PROCEDURE dbo.usp_GetLastName
    @EmployeeID int,
    @EmployeeStatus int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM dbo.Employees WHERE EmployeeID = @EmployeeID)
    BEGIN
        SET @EmployeeStatus = 1;

        SELECT LastName
        FROM dbo.Employees
        WHERE EmployeeID = @EmployeeID;
    END
    ELSE
    BEGIN
        SET @EmployeeStatus = 0;

        SELECT CAST(NULL AS nvarchar(50)) AS LastName
        WHERE 1 = 0;
    END
END;

The following script executes the procedure for @EmployeeID = 101 and captures the returned status value into a variable:

DECLARE @EmployeeStatus int;

EXEC dbo.usp_GetLastName
     @EmployeeID = 101,
     @EmployeeStatus = @EmployeeStatus OUTPUT;

SELECT @EmployeeStatus AS EmployeeStatus;

This pattern works because @EmployeeStatus is a variable that can receive a value, and the call includes the OUTPUT keyword.