SQL Server Documentation

Executing Stored Procedures

This section details how to execute stored procedures in SQL Server, covering various methods and considerations.

Overview of Execution

Stored procedures offer a powerful way to encapsulate T-SQL logic for execution. They can be invoked using the EXECUTE or EXEC statement.

Basic Execution

To execute a stored procedure, you simply use the EXECUTE keyword followed by the procedure name.

Syntax

EXECUTE procedure_name [ argument_list ] ;

Or the shorthand:

EXEC procedure_name [ argument_list ] ;

Example

Consider a simple stored procedure named usp_GetCustomerByID that takes a CustomerID as input.

EXEC usp_GetCustomerByID @CustomerID = 101;

Executing with Parameters

Stored procedures often accept parameters to customize their behavior. Parameters can be passed by position or by name.

Passing Parameters by Name (Recommended)

This method is more robust as it is not dependent on the order of parameters defined in the procedure.

EXECUTE procedure_name @parameter1 = value1, @parameter2 = value2;

Passing Parameters by Position

Parameters are matched based on their order in the procedure definition.

EXECUTE procedure_name value1, value2;
Important: If you mix positional and named parameters, the named parameters must come after any positional parameters. However, it is generally best practice to use named parameters exclusively to avoid confusion and potential errors.

Returning Values

Stored procedures can return values through:

Executing a Procedure with an OUTPUT Parameter

DECLARE @OutputValue INT;
EXECUTE usp_GetOrderCountByCustomerID @CustomerID = 50, @OrderCount = @OutputValue OUTPUT;
SELECT @OutputValue AS TotalOrders;

Executing a Procedure and Capturing the RETURN Value

DECLARE @ReturnStatus INT;
EXEC @ReturnStatus = usp_ProcessOrder @OrderID = 123;
IF @ReturnStatus = 0
BEGIN
    PRINT 'Order processed successfully.';
END
ELSE
BEGIN
    PRINT 'Order processing failed.';
END

Executing System Stored Procedures

System stored procedures, often prefixed with sp_, are used to manage and administer SQL Server.

EXEC sp_help 'Customers';

Executing from within another Stored Procedure or Batch

When executing a procedure from within another T-SQL context, you can also use the EXECUTE statement. If the called procedure returns a result set, it will be returned to the caller.

Security Considerations

Ensure that users have appropriate execute permissions on stored procedures. Avoid granting broad permissions to execute all procedures.

Security Warning: Be cautious when executing procedures that perform data modification. Always validate the input parameters and ensure the procedure logic is secure.

Best Practices