Stored Procedures

Stored procedures are a set of one or more Transact-SQL statements that are compiled and stored on the database server. They can be executed repeatedly by multiple users or applications. Stored procedures offer several benefits, including improved performance, enhanced security, and modularity of code.

Key Concepts

Pre-compiled Execution: Stored procedures are compiled once and then stored, which can lead to faster execution times compared to ad-hoc SQL statements.
Parameterization: Procedures can accept input parameters and return output parameters, making them highly flexible.
Modularity: Complex database operations can be encapsulated within a single stored procedure, simplifying application logic.
Security: Permissions can be granted on stored procedures, allowing users to execute specific operations without direct access to underlying tables.
Reduced Network Traffic: Instead of sending multiple SQL statements over the network, only the call to the stored procedure is sent.

Creating a Stored Procedure

You can create a stored procedure using the CREATE PROCEDURE statement. Here's a basic example:

Basic Stored Procedure Example


CREATE PROCEDURE usp_GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SELECT
        OrderID,
        OrderDate,
        TotalAmount
    FROM
        Orders
    WHERE
        CustomerID = @CustomerID;
END;

Explanation:

CREATE PROCEDURE usp_GetCustomerOrders: This defines the start of a new stored procedure named usp_GetCustomerOrders. The usp_ prefix is a common convention for user-defined stored procedures.
@CustomerID INT: This declares an input parameter named @CustomerID of integer type.
AS: This keyword separates the procedure definition from its body.
BEGIN...END: These keywords define the block of Transact-SQL statements that the procedure will execute.
SELECT ... FROM Orders WHERE CustomerID = @CustomerID;: This is the core logic, retrieving order details for the specified customer.

Executing a Stored Procedure

You can execute a stored procedure using the EXECUTE or EXEC statement:

Executing the Stored Procedure


EXEC usp_GetCustomerOrders @CustomerID = 101;
-- or
EXEC usp_GetCustomerOrders 101;

Stored Procedure Options

Stored procedures have various options that can control their behavior. Some common options include:

WITH RECOMPILE: Forces the procedure to be recompiled each time it's executed. This can be useful for procedures with highly variable query plans but can impact performance.
WITH ENCRYPTION: Encrypts the procedure's source code, preventing it from being viewed as plain text.

Modifying and Dropping Stored Procedures

You can modify an existing stored procedure using ALTER PROCEDURE and remove it using DROP PROCEDURE.

Modifying and Dropping


-- Modifying a procedure
ALTER PROCEDURE usp_GetCustomerOrders
    @CustomerID INT,
    @OrderDateFrom DATETIME = NULL
AS
BEGIN
    SELECT
        OrderID,
        OrderDate,
        TotalAmount
    FROM
        Orders
    WHERE
        CustomerID = @CustomerID
        AND (@OrderDateFrom IS NULL OR OrderDate >= @OrderDateFrom);
END;

-- Dropping a procedure
DROP PROCEDURE usp_GetCustomerOrders;