Executing Stored Procedures

Stored procedures are precompiled SQL statements that can be executed as a single unit. They offer benefits such as improved performance, enhanced security, and modularity. This section details how to execute a stored procedure in SQL Server.

The EXECUTE Statement

The primary way to execute a stored procedure is by using the EXECUTE or EXEC keyword, followed by the procedure name.

Syntax:

EXECUTE procedure_name [ parameter1 = value1, parameter2 = value2, ... ] ;

Or the shorthand:

EXEC procedure_name [ parameter1 = value1, parameter2 = value2, ... ] ;

Example: Executing a Simple Procedure

Let's assume you have a stored procedure named GetEmployeeList that doesn't take any parameters. You would execute it as follows:

EXEC GetEmployeeList;

Executing Procedures with Parameters

If a stored procedure accepts parameters, you can pass values to them by name or by position. Passing by name is generally recommended for clarity and robustness, especially when dealing with multiple parameters.

Passing Parameters by Name

Consider a procedure GetEmployeeByID that takes an @EmployeeID parameter.

EXEC GetEmployeeByID @EmployeeID = 101;

If the procedure had multiple parameters, for example, @EmployeeID and @Department:

EXEC GetEmployeeByIDAndDepartment @EmployeeID = 101, @Department = 'Sales';

Passing Parameters by Position

You can also pass parameter values in the order they are defined in the procedure. However, this is less flexible and can lead to errors if the procedure definition changes.

-- Assuming @EmployeeID is the first parameter and @Department is the second
                EXEC GetEmployeeByIDAndDepartment 101, 'Sales';

Important: Mixing positional and named parameters is not allowed in a single EXECUTE statement. If you use named parameters, all subsequent parameters must also be specified by name.

Executing Procedures from Other Procedures

You can call one stored procedure from within another. This is a powerful technique for building complex logic.

-- Inside another stored procedure
                CREATE PROCEDURE ProcessNewOrder (@OrderID INT)
                AS
                BEGIN
                    -- ... other logic ...
                    EXEC UpdateOrderStatus @OrderID = @OrderID, @Status = 'Processing';
                    -- ... more logic ...
                END;

Output Parameters

Stored procedures can have output parameters that return values to the caller. To capture these values, you need to declare variables in your calling script.

Example: Procedure GetEmployeeCountByDepartment with an output parameter @EmployeeCount.

DECLARE @Count INT;
                EXEC GetEmployeeCountByDepartment @Department = 'IT', @EmployeeCount = @Count OUTPUT;
                SELECT 'Number of employees in IT:', @Count;

The OUTPUT keyword is crucial when declaring the variable that will receive the output parameter's value.

Return Values

Stored procedures can also return an integer status code using the RETURN statement. This is typically used to indicate success or failure. A return value of 0 usually signifies success.

DECLARE @ReturnStatus INT;
                EXEC @ReturnStatus = MyStoredProcedure;
                IF @ReturnStatus = 0
                    PRINT 'Procedure executed successfully.';
                ELSE
                    PRINT 'Procedure failed with status code: ' + CAST(@ReturnStatus AS VARCHAR);

Permissions Required

To execute a stored procedure, a user must have the EXECUTE permission on that procedure. This permission can be granted at the individual procedure level or for all procedures in a schema.