Stored Procedures (SQL Server)

This document provides a comprehensive reference for understanding and working with stored procedures in Microsoft SQL Server.

What are Stored Procedures?

A stored procedure is a prepared SQL statement or a set of SQL statements that is stored on the database server. Applications can call the stored procedure, and the procedure executes the commands. This offers several advantages, including:

• Performance: Stored procedures can be pre-compiled and cached by the database, leading to faster execution.
• Security: Permissions can be granted to execute a stored procedure without granting direct permissions on the underlying tables, enhancing data security.
• Modularity and Reusability: Encapsulate complex logic into a single unit that can be called from multiple places.
• 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

The basic syntax for creating a stored procedure is as follows:

CREATE PROCEDURE procedure_name
                [ (@parameter1 datatype [= default_value] [OUTPUT],
                  @parameter2 datatype [= default_value] [OUTPUT],
                  ...) ]
            AS
            BEGIN
                -- SQL statements here
                -- Can include SELECT, INSERT, UPDATE, DELETE, control flow, etc.
            END;
            GO
            

Key Components:

• CREATE PROCEDURE procedure_name: Initiates the creation of a stored procedure named procedure_name.
• @parameter1 datatype: Defines input or output parameters with their data types.
• [= default_value]: Optionally specifies a default value for a parameter.
• [OUTPUT]: Indicates that the parameter is an output parameter.
• AS: Separates the procedure definition from its body.
• BEGIN...END: Encloses the Transact-SQL statements that make up the procedure's logic.
• GO: A batch separator used by SQL Server utilities.

Example: Creating a Simple Stored Procedure

This procedure retrieves customer details based on a customer ID.

CREATE PROCEDURE usp_GetCustomerDetails
                @CustomerID INT
            AS
            BEGIN
                SELECT CustomerID, FirstName, LastName, Email
                FROM Customers
                WHERE CustomerID = @CustomerID;
            END;
            GO
            

Executing a Stored Procedure

Stored procedures can be executed using the EXECUTE or EXEC command.

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

            -- Or using shorthand
            EXEC procedure_name value1, value2, ...;
            GO
            

Example: Executing the Stored Procedure

EXEC usp_GetCustomerDetails @CustomerID = 101;
            GO
            

Stored Procedure Parameters

Stored procedures can accept input parameters to customize their behavior and return output parameters to pass results back to the caller.

Input Parameters

These parameters allow you to pass values into the procedure. If a default value is defined, the parameter is optional.

Output Parameters

These parameters are used to return values from the procedure to the calling application or script. They are declared with the OUTPUT keyword.

CREATE PROCEDURE usp_GetCustomerCount
                @ProductCategory VARCHAR(50),
                @CustomerCount INT OUTPUT
            AS
            BEGIN
                SELECT @CustomerCount = COUNT(DISTINCT c.CustomerID)
                FROM Customers c
                JOIN Orders o ON c.CustomerID = o.CustomerID
                JOIN OrderDetails od ON o.OrderID = od.OrderID
                JOIN Products p ON od.ProductID = p.ProductID
                WHERE p.Category = @ProductCategory;
            END;
            GO

            -- Executing with an output parameter
            DECLARE @Count INT;
            EXEC usp_GetCustomerCount @ProductCategory = 'Electronics', @CustomerCount = @Count OUTPUT;
            SELECT @Count AS NumberOfCustomers;
            GO
            

Modifying and Dropping Stored Procedures

Modifying a Stored Procedure

Use the ALTER PROCEDURE statement to modify an existing stored procedure.

ALTER PROCEDURE procedure_name
                [ (@parameter1 datatype [= default_value] [OUTPUT], ...) ]
            AS
            BEGIN
                -- New SQL statements here
            END;
            GO
            

Dropping a Stored Procedure

Use the DROP PROCEDURE statement to remove a stored procedure.

DROP PROCEDURE procedure_name;
            GO
            

Advanced Concepts

• Error Handling: Implement robust error handling using TRY...CATCH blocks and system functions like @@ERROR.
• Dynamic SQL: Construct and execute SQL statements dynamically using sp_executesql.
• Transaction Management: Control transactions within stored procedures using BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.
• Recursion: Stored procedures can call themselves (recursive stored procedures), useful for hierarchical data.