SQL Stored Procedure Best Practices

Over the past year, I've been involved in data-heavy ETL projects where most of the logic is written directly in SQL. Along the way, I\ve picked up a set of practical habits and patterns that consistently made my work easier and more maintainable. This post summarizes some of those best practices for writing clean, readable, and robust stored procedures in SQL Server (MSQL), with quick examples for each.

Whether you're building data pipelines, handling transformations, or simply maintaining a legacy system, these techniques will help you and your team write better SQL.

1. Format and Lint Your Code Nicely

Why: Clean, consistently formatted code is easier to read, debug, and maintain.

SELECT
    c.CustomerID AS CustomerNumber,
    c.Name       AS Name,
    o.OrderID    AS OrderNumber
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE c.IsActive = 1;

2. Avoid Loops and Cursors

Why: Loops and cursors are often slow and unnecessary. Set-based operations are more efficient in SQL.

-- ❌ Cursor-based approach (slow)
DECLARE myCursor CURSOR FOR SELECT ...

-- ✅ Set-based alternative
INSERT INTO Orders (CustomerID, OrderValue)
SELECT CustomerID, OrderValue FROM StagingOrders;

3. Use Functions for Reusable Logic

Why: Avoid duplicating logic that doesn't perform data writes. Functions make your code DRY and readable.

CREATE FUNCTION dbo.fn_FormatName (@Name NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
BEGIN
    RETURN UPPER(LTRIM(RTRIM(@Name)))
END

4. Name Procedures Clearly

Why: Consistent naming helps with organization and discoverability.

Format I use: sp<TableName>_<Action>_<Details>

-- Good
CREATE PROCEDURE spCustomer_Insert_FromStaging

-- Bad
CREATE PROCEDURE proc1

5. Add Comments to Each Stored Procedure

Why: To help others (and your future self) quickly understand the purpose and structure of the procedure. Specially if stored procedure names are not clearly enough.

------------------------------------
-- Author: author
-- Created: 2024-01-01
-- Description: Inserts customers from staging
------------------------------------
CREATE PROCEDURE spCustomer_Insert_FromStaging
AS
BEGIN
    -- procedure logic here
END

6. Use COMMIT and ROLLBACK for Critical Logic

Why: Ensure consistency and prevent partial updates, especially in large transactions or sensitive operations.

BEGIN TRY
    BEGIN TRANSACTION

    -- Insert/update logic

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    -- Optional: Log or rethrow the error
END CATCH

7. Use TRY...CATCH for Error Handling

Why: Prevents unexpected crashes and provides a way to gracefully handle errors. Of course this is optional in the case where your application is expected to catch SQL exceptions.

BEGIN TRY
    -- Risky operation
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH

8. Create a Logging Table for Audit Trails

Why: Makes it easier to debug and monitor procedure executions, especially in large ETL systems.

INSERT INTO ProcedureLog (ProcName, RunTime, Status, Detail)
VALUES ('spCustomer_Insert_FromStaging', GETDATE(), 'SUCCESS', 'Inserted N records ...')

9. Use Output Parameters for Integration

Why: Useful for returning values or messages back to calling applications or orchestrators.

CREATE PROCEDURE spCustomer_Insert
    @OutputMessage NVARCHAR(MAX) OUTPUT
AS
BEGIN
    INSERT INTO Customers (Name) VALUES ('John')
    SET @OutputMessage = 'Customer John inserted successfully.'
END

Final Thoughts

If you work in a codebase where SQL drives most of the business logic, whether for ETL, analytics, or reporting, investing in clean practices pays off. These guidelines helped me debug less, collaborate more, and scale processes more confidently.

You don't need to be a SQL expert to write good stored procedures, just thoughtful and consistent. Start with these basics and adapt them to your team's workflow.