Advanced Stored Procedures and Error Handling

Difficulty: Advanced
Generated on: 2025-07-10 02:31:25
Category: SQL Cheatsheet for Database Development

---

Advanced Stored Procedures and Error Handling - SQL Cheatsheet

1. Quick Overview

Stored procedures are precompiled SQL code blocks stored in the database. They provide modularity, reusability, and security advantages. Advanced usage includes complex logic, transaction management, dynamic SQL, and robust error handling. Use stored procedures when:

• You need to encapsulate complex business logic within the database.
• You want to improve performance by reducing network traffic.
• You need to enforce data integrity and security.
• You want to manage transactions and ensure data consistency.
• You need to handle errors gracefully and consistently.

2. Syntax

Basic Stored Procedure (SQL Server Example):

CREATE PROCEDURE dbo.MyProcedure
    @InputParam INT,
    @OutputParam VARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON; -- Suppress row count messages

    -- Procedure logic here
    SELECT @OutputParam = 'Value based on ' + CAST(@InputParam AS VARCHAR(10));

    RETURN 0; -- Optional: Return a status code
END;
GO

-- Execute the procedure
DECLARE @Result VARCHAR(255);
EXEC dbo.MyProcedure 123, @Result OUTPUT;
SELECT @Result;

Error Handling (SQL Server Example):

CREATE PROCEDURE dbo.MyProcedureWithErrors
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- Risky operation
        INSERT INTO MyTable (Column1) VALUES (NULL); -- Deliberate error

        SELECT 'Operation Succeeded!';
    END TRY
    BEGIN CATCH
        -- Error handling logic
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;

        -- Optionally: Raise an error to the caller
        RAISERROR('An error occurred in the procedure.', 16, 1);
    -- or use THROW (SQL Server 2012+)
    -- THROW 50001, 'An error occurred in the procedure.', 1;

        RETURN -1; -- Indicate failure
    END CATCH;

    RETURN 0; -- Indicate success
END;
GO

EXEC dbo.MyProcedureWithErrors;

Transaction Management (SQL Server Example):

CREATE PROCEDURE dbo.MyTransactionalProcedure
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    BEGIN TRY
        -- Multiple operations within the transaction
        UPDATE MyTable SET Column1 = 'Value1' WHERE ID = 1;
        INSERT INTO MyTable (Column1) VALUES ('Value2');

        -- If everything is successful, commit the transaction
        COMMIT TRANSACTION;

        SELECT 'Transaction committed successfully!';
    END TRY
    BEGIN CATCH
        -- If any error occurs, rollback the transaction
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;

        THROW; -- Re-raise the error to the caller (SQL Server 2012+)
    --RAISERROR('Transaction rolled back due to error.', 16, 1); -- For older versions

        RETURN -1; -- Indicate failure
    END CATCH;

    RETURN 0; -- Indicate success
END;
GO

EXEC dbo.MyTransactionalProcedure;

Dynamic SQL (SQL Server Example):

CREATE PROCEDURE dbo.MyDynamicProcedure
    @TableName SYSNAME,
    @ColumnName SYSNAME,
    @SearchValue VARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);

    SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) +
               N' WHERE ' + QUOTENAME(@ColumnName) + N' = @SearchValue';

    -- Execute the dynamic SQL using sp_executesql to prevent SQL injection
    EXEC sp_executesql @SQL,
                       N'@SearchValue VARCHAR(255)',
                       @SearchValue = @SearchValue;
END;
GO

-- Example Usage
EXEC dbo.MyDynamicProcedure 'MyTable', 'Column1', 'SomeValue';

Cursor Usage (SQL Server Example):

CREATE PROCEDURE dbo.MyCursorProcedure
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CustomerID INT;
    DECLARE @CustomerName VARCHAR(255);

    -- Declare the cursor
    DECLARE CustomerCursor CURSOR FOR
    SELECT CustomerID, CustomerName FROM Customers;

    -- Open the cursor
    OPEN CustomerCursor;

    -- Fetch the first row
    FETCH NEXT FROM CustomerCursor INTO @CustomerID, @CustomerName;

    -- Loop through the cursor
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Process the current row
        PRINT 'CustomerID: ' + CAST(@CustomerID AS VARCHAR(10)) + ', CustomerName: ' + @CustomerName;

        -- Fetch the next row
        FETCH NEXT FROM CustomerCursor INTO @CustomerID, @CustomerName;
    END;

    -- Close the cursor
    CLOSE CustomerCursor;

    -- Deallocate the cursor
    DEALLOCATE CustomerCursor;
END;
GO

EXEC dbo.MyCursorProcedure;

3. Common Use Cases

• Data Validation: Implement complex validation rules before inserting or updating data.
• Auditing: Log changes to data for compliance and tracking purposes.
• Reporting: Generate complex reports by aggregating and filtering data.
• Batch Processing: Perform bulk operations on large datasets efficiently.
• Integration with Other Systems: Expose data and functionality to other applications through stored procedures.
• Security Enforcement: Control access to data and functionality based on user roles and permissions.
• ETL (Extract, Transform, Load) Processes: Centralize data transformation logic within the database.
• Workflow Automation: Orchestrate complex workflows by chaining together multiple stored procedures.

4. Best Practices

• Naming Conventions: Use consistent and descriptive names for stored procedures and parameters. (e.g., sp_UpdateCustomerAddress, @CustomerID).
• Parameter Validation: Validate all input parameters to prevent errors and SQL injection.
• Error Handling: Implement robust error handling using TRY...CATCH blocks. Log errors appropriately.
• Transaction Management: Use transactions to ensure data consistency and atomicity. Keep transactions as short as possible.
• Security: Use parameterized queries to prevent SQL injection. Grant execute permissions only to authorized users.
• Performance:
  • Minimize network traffic by reducing the number of round trips between the application and the database.
  • Use SET NOCOUNT ON to suppress row count messages.
  • Optimize queries within the stored procedure using indexes and appropriate data types.
  • Avoid cursors if possible; use set-based operations instead.
  • Consider using compiled stored procedures for frequently executed procedures.
• Code Comments: Add clear and concise comments to explain the purpose and logic of the stored procedure.
• Modular Design: Break down complex stored procedures into smaller, more manageable modules.
• Version Control: Store stored procedure definitions in version control to track changes and facilitate rollback.
• Testing: Thoroughly test stored procedures with different input values and error conditions.
• Avoid SELECT *: Specify the columns you need to improve performance and reduce the risk of breaking the procedure if the table structure changes.
• Index Optimization: Ensure appropriate indexes exist on tables used within the procedure.

5. Examples

Example 1: Order Processing with Transaction Management (SQL Server)

CREATE PROCEDURE dbo.ProcessOrder
    @CustomerID INT,
    @ProductID INT,
    @Quantity INT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    BEGIN TRY
        -- Check if the customer exists
        IF NOT EXISTS (SELECT 1 FROM Customers WHERE CustomerID = @CustomerID)
        BEGIN
            RAISERROR('Invalid CustomerID.', 16, 1);
            RETURN -1;
        END

        -- Check if the product exists
        IF NOT EXISTS (SELECT 1 FROM Products WHERE ProductID = @ProductID)
        BEGIN
            RAISERROR('Invalid ProductID.', 16, 1);
            RETURN -1;
        END

        -- Check if there is enough stock
        IF (SELECT QuantityInStock FROM Products WHERE ProductID = @ProductID) < @Quantity
        BEGIN
            RAISERROR('Insufficient stock.', 16, 1);
            RETURN -1;
        END

        -- Update the stock quantity
        UPDATE Products
        SET QuantityInStock = QuantityInStock - @Quantity
        WHERE ProductID = @ProductID;

        -- Insert a new order record
        INSERT INTO Orders (CustomerID, ProductID, Quantity, OrderDate)
        VALUES (@CustomerID, @ProductID, @Quantity, GETDATE());

        -- Commit the transaction
        COMMIT TRANSACTION;

        SELECT 'Order processed successfully!';
        RETURN 0;
    END TRY
    BEGIN CATCH
        -- Rollback the transaction if any error occurs
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;

        THROW; -- Re-raise the error to the caller (SQL Server 2012+)

        RETURN -1;
    END CATCH;
END;
GO

-- Sample Data (assuming you have Customers and Products tables)
-- INSERT INTO Customers (CustomerID, CustomerName) VALUES (1, 'John Doe');
-- INSERT INTO Products (ProductID, ProductName, QuantityInStock) VALUES (101, 'Widget', 100);

-- Execute the procedure
EXEC dbo.ProcessOrder 1, 101, 5;

Example 2: Dynamic Pivot Table (SQL Server)

This example creates a dynamic pivot table, which can be very useful for reporting.

CREATE PROCEDURE dbo.DynamicPivot
    @ColumnToPivot NVARCHAR(128),
    @TableName NVARCHAR(128),
    @AggregationColumn NVARCHAR(128),
    @WhereClause NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX),
            @Columns NVARCHAR(MAX);

    -- Dynamically build the list of columns to pivot on
    SET @SQL = N'SELECT @Columns = STRING_AGG(QUOTENAME(' + @ColumnToPivot + '), '','') WITHIN GROUP (ORDER BY ' + @ColumnToPivot + ') ' +
               N'FROM (SELECT DISTINCT ' + @ColumnToPivot + ' FROM ' + QUOTENAME(@TableName) +
               CASE WHEN @WhereClause IS NOT NULL THEN N' WHERE ' + @WhereClause ELSE N'' END +
               N') AS PivotColumns';

    EXEC sp_executesql @SQL, N'@Columns NVARCHAR(MAX) OUTPUT', @Columns OUTPUT;

    -- Build the dynamic SQL statement
    SET @SQL = N'SELECT * FROM (SELECT ' + @ColumnToPivot + ', ' + @AggregationColumn +
               N' FROM ' + QUOTENAME(@TableName) +
               CASE WHEN @WhereClause IS NOT NULL THEN N' WHERE ' + @WhereClause ELSE N'' END +
               N') AS SourceTable PIVOT (SUM(' + @AggregationColumn + ') FOR ' + @ColumnToPivot +
               N' IN (' + @Columns + ')) AS PivotTable';

    -- Execute the dynamic SQL
    EXEC sp_executesql @SQL;

END;
GO

-- Sample Usage (requires AdventureWorks database)
-- Assuming you have a ProductInventory table with columns LocationID, ProductID, Quantity
-- EXEC dbo.DynamicPivot @ColumnToPivot = 'LocationID', @TableName = 'Production.ProductInventory', @AggregationColumn = 'Quantity';

Example 3: User Defined Function (UDF) in a Stored Procedure (SQL Server):

-- Create a sample UDF
CREATE FUNCTION dbo.CalculateDiscount (@Price DECIMAL(10, 2), @DiscountRate DECIMAL(5, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @DiscountedPrice DECIMAL(10, 2);
    SET @DiscountedPrice = @Price * (1 - @DiscountRate);
    RETURN @DiscountedPrice;
END;
GO

-- Create a stored procedure that uses the UDF
CREATE PROCEDURE dbo.ApplyDiscounts
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE Products
    SET Price = dbo.CalculateDiscount(Price, 0.10) -- Apply a 10% discount
    WHERE Category = 'Electronics';

    SELECT 'Discounts applied successfully!';
END;
GO

-- Sample Data (assuming you have a Products table with columns Price, Category)
-- INSERT INTO Products (ProductName, Category, Price) VALUES ('Laptop', 'Electronics', 1000.00);

-- Execute the procedure
EXEC dbo.ApplyDiscounts;

6. Common Pitfalls

• SQL Injection: Failing to use parameterized queries or properly escape input values can lead to SQL injection vulnerabilities.
• Deadlocks: Long-running transactions or conflicting lock requests can cause deadlocks. Design transactions carefully.
• Performance Bottlenecks: Inefficient queries, missing indexes, or excessive use of cursors can degrade performance.
• Unclear Error Handling: Not handling errors properly can lead to unexpected behavior and data corruption.
• Lack of Documentation: Poorly documented stored procedures can be difficult to maintain and debug.
• Ignoring Return Codes: Not checking the return codes of stored procedures called from other procedures or applications can mask errors.
• Over-reliance on Cursors: Cursors are generally slower than set-based operations. Avoid them if possible.
• Excessive Locking: Holding locks for longer than necessary can block other transactions.
• Not using SET NOCOUNT ON: Causes extra network traffic with each row processed.
• Incorrect Data Types: Using incorrect data types for parameters or variables can lead to unexpected results or errors.

Troubleshooting Tips:

• Use Profiler/Extended Events: Use SQL Server Profiler (deprecated, use Extended Events instead) or similar tools in other databases to trace the execution of stored procedures and identify performance bottlenecks or errors.
• Review Error Logs: Check the database error logs for any errors or warnings related to stored procedures.
• Debug Stored Procedures: Use the debugger in SQL Server Management Studio (or similar tools) to step through the code and inspect variables.
• Test with Sample Data: Create a test environment with sample data to reproduce and debug issues.
• Simplify the Procedure: Comment out sections of code to isolate the source of the problem.

7. Database Variations

Here’s a table highlighting some key differences across popular database systems regarding stored procedures and error handling:

Feature	MySQL	PostgreSQL	SQL Server	Oracle
Syntax	CREATE PROCEDURE ...	CREATE OR REPLACE FUNCTION ...	CREATE PROCEDURE ...	CREATE OR REPLACE PROCEDURE ...
Transaction Handling	START TRANSACTION, COMMIT, ROLLBACK	BEGIN, COMMIT, ROLLBACK	BEGIN TRANSACTION, COMMIT, ROLLBACK	BEGIN, COMMIT, ROLLBACK
Error Handling	DECLARE CONTINUE HANDLER (limited)	BEGIN ... EXCEPTION ... END	TRY ... CATCH	BEGIN ... EXCEPTION ... END
Dynamic SQL	PREPARE, EXECUTE	EXECUTE (using format() for safety)	sp_executesql	EXECUTE IMMEDIATE
Output Parameters	@param VARCHAR(255) OUT	param VARCHAR(255) (using INOUT mode)	@param VARCHAR(255) OUTPUT	param VARCHAR2(255) OUT
Return Values	Limited; often use OUT parameters.	RETURN (can return any data type)	RETURN (integer status code)	RETURN (cannot return a value directly)
Cursors	Supported	Supported	Supported	Supported
Compound Statements	BEGIN ... END	BEGIN ... END	BEGIN ... END	BEGIN ... END

MySQL Notes:

• Error handling is less sophisticated than other systems. DECLARE CONTINUE HANDLER is used for simple error trapping.
• Transactions are enabled through the use of storage engines like InnoDB.

PostgreSQL Notes:

• Stored procedures are implemented as functions. Use CREATE OR REPLACE FUNCTION to update an existing procedure.
• The EXCEPTION block provides robust error handling. You can catch specific exceptions or use a general OTHERS handler.
• PostgreSQL is more strict about data types than some other databases.

SQL Server Notes:

• TRY...CATCH blocks provide structured error handling.
• sp_executesql is the preferred method for executing dynamic SQL to prevent SQL injection.
• THROW (SQL Server 2012+) is the preferred way to re-raise exceptions.
• @@ERROR is deprecated in favor of TRY...CATCH blocks.

Oracle Notes:

• Stored procedures are compiled PL/SQL blocks.
• EXCEPTION blocks handle errors. You can catch specific exceptions or use a general OTHERS handler.
• EXECUTE IMMEDIATE executes dynamic SQL. Bind variables (USING) are used to prevent SQL injection.
• RAISE_APPLICATION_ERROR raises custom exceptions.
• Oracle does not directly return a value from stored procedures using RETURN. Use OUT parameters instead.

This cheatsheet provides a solid foundation for developing advanced stored procedures and implementing robust error handling in your SQL code. Remember to consult the specific documentation for your database system for the most up-to-date information and best practices. Always prioritize security, performance, and maintainability in your stored procedure design.