Microsoft Docs

Stored Procedures: Best Practices

This document outlines best practices for designing, developing, and maintaining stored procedures in Microsoft SQL Server to ensure optimal performance, security, and maintainability.

1. Naming Conventions

Consistent naming conventions improve readability and understanding.

• Use a clear and descriptive prefix for stored procedure names (e.g., usp_ for user stored procedures, sps_ for system stored procedures).
• Use PascalCase or camelCase for object names.
• Avoid using reserved keywords or abbreviations that might be ambiguous.
• Include the primary action the procedure performs (e.g., usp_GetCustomerByID, usp_UpdateOrderQuantity).

2. Error Handling

Robust error handling is crucial for reliable applications.

• Use TRY...CATCH blocks to gracefully handle errors.
• Inside the CATCH block, log the error details using functions like ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), and ERROR_MESSAGE().
• Consider using THROW to re-throw errors after logging or to propagate custom errors.
• Use RAISERROR for backward compatibility, but prefer THROW for new development.

-- Example of TRY...CATCH
BEGIN TRY
    -- Your SQL statements here
    SELECT * FROM NonExistentTable; -- Simulate an error
END TRY
BEGIN CATCH
    -- Log error details
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
    PRINT 'Error Message: ' + @ErrorMessage;

    -- Optionally re-throw the error
    THROW @ErrorSeverity, @ErrorMessage, @ErrorState;
END CATCH
        

3. Performance Optimization

Writing efficient stored procedures is key to database performance.

• Avoid using SELECT *; explicitly list the columns you need.
• Minimize the use of cursors; prefer set-based operations.
• Use parameterized queries to prevent SQL injection and allow query plan reuse.
• Be mindful of N+1 query problems.
• Consider using table variables or temporary tables appropriately. Table variables are generally preferred for smaller datasets and when their scope is limited to a single batch.
• Ensure proper indexing on tables accessed by the stored procedure.
• Use SET NOCOUNT ON at the beginning of your stored procedures to suppress the message indicating the number of rows affected by a Transact-SQL statement. This can reduce network traffic, especially for procedures that perform many operations.

4. Security

Implement proper security measures to protect your data.

• Use parameterized queries to prevent SQL injection attacks.
• Grant the least privilege necessary to the users or roles executing the stored procedures.
• Avoid dynamic SQL whenever possible. If dynamic SQL is necessary, use sp_executesql with parameters for security and performance.
• Consider using schema binding for views and stored procedures to maintain integrity.

-- Example using sp_executesql
DECLARE @CustomerID INT = 123;
DECLARE @SQL NVARCHAR(500);

SET @SQL = N'SELECT CustomerName, Email FROM Customers WHERE CustomerID = @CustID';

EXEC sp_executesql @SQL, N'@CustID INT', @CustID = @CustomerID;
        

5. Maintainability and Readability

Well-structured code is easier to understand and modify.

• Break down complex logic into smaller, reusable stored procedures.
• Add comments to explain complex logic, business rules, or non-obvious code.
• Use consistent indentation and formatting.
• Document the purpose, parameters, and return values of your stored procedures.
• Avoid nesting stored procedures too deeply, as it can make debugging difficult.

6. Transactions

Manage transactions effectively to ensure data consistency.

• Use BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION to control transaction scope.
• Ensure that all operations within a transaction are committed or rolled back together.
• Keep transactions as short as possible to minimize blocking.
• Consider using XACT_ABORT ON to ensure that if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

SET NOCOUNT ON;
SET XACT_ABORT ON; -- Ensures transaction rollback on error

BEGIN TRY
    BEGIN TRANSACTION;

    -- Operation 1
    UPDATE Products SET StockQuantity = StockQuantity - 1 WHERE ProductID = 101;

    -- Operation 2
    INSERT INTO OrderDetails (OrderID, ProductID, Quantity) VALUES (500, 101, 1);

    COMMIT TRANSACTION;
    PRINT 'Transaction committed successfully.';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    PRINT 'Transaction rolled back due to error.';
    -- Log the error details as shown in the Error Handling section
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    THROW 50001, @ErrorMessage, 1;
END CATCH
        

7. Return Values and Output Parameters

Understand different ways to return data from stored procedures.

• Use SELECT statements to return result sets.
• Use OUTPUT parameters for returning single scalar values.
• Use the RETURN statement for returning an integer status code (conventionally 0 for success, non-zero for failure).