Real-World Stored Procedure: Shop Sales Trend Report

PROCEDURE : dbo.usp_GetShopSalesTrend
---------------------------------------------------------------------
Purpose  : Shows each month's sales amount and whether it went
           UP or DOWN compared with the previous month (uses LAG).
           Optionally saves the result into dbo.ShopSalesTrendReport.

(it creates and fills dbo.ShopSales)

Parameters :

@FromMonth    INT  = 1    first month to show (1 - 12)
@ToMonth      INT  = 12   last month to show  (1 - 12)
@SaveToReport BIT  = 0    1 = also save the result into the report table

Transaction management :

Reading data does not need a transaction.
Saving the report = DELETE old rows + INSERT new rows. Both steps run
inside ONE transaction, so either both are saved or neither is.

Error handling :

• Input is validated first; bad input raises an error with THROW.
• TRY...CATCH rolls back an open transaction, logs the error into dbo.ProcErrorLog, then re-throws it to the caller.
• SET XACT_ABORT ON makes sure any runtime error rolls back everything.

Return codes : 0 = success   (errors are raised, not returned)

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Supporting tables (created only if they do not exist yet)

-- Saved trend results
IF OBJECT_ID('dbo.ShopSalesTrendReport', 'U') IS NULL
CREATE TABLE dbo.ShopSalesTrendReport
(
    ReportID      INT IDENTITY(1,1) PRIMARY KEY,
    SaleMonth     INT          NOT NULL,
    MonthName     VARCHAR(10)  NOT NULL,
    Amount        INT          NOT NULL,
    PreviousMonth INT          NULL,
    Trend         VARCHAR(5)   NOT NULL,
    CreatedAt     DATETIME2(0) NOT NULL DEFAULT SYSDATETIME()
);
GO

-- Errors raised inside procedures
IF OBJECT_ID('dbo.ProcErrorLog', 'U') IS NULL
CREATE TABLE dbo.ProcErrorLog
(
    ErrorLogID     INT IDENTITY(1,1) PRIMARY KEY,
    ProcedureName  NVARCHAR(128)  NULL,
    ErrorNumber    INT            NULL,
    ErrorSeverity  INT            NULL,
    ErrorState     INT            NULL,
    ErrorLine      INT            NULL,
    ErrorMessage   NVARCHAR(4000) NULL,
    Parameters     NVARCHAR(500)  NULL,
    LoggedAt       DATETIME2(0)   NOT NULL DEFAULT SYSDATETIME()
);
GO

The stored procedure

CREATE OR ALTER PROCEDURE dbo.usp_GetShopSalesTrend
    @FromMonth    INT = 1,
    @ToMonth      INT = 12,
    @SaveToReport BIT = 0
AS
BEGIN
    SET NOCOUNT ON;      -- do not send "(n rows affected)" messages
    SET XACT_ABORT ON;   -- any runtime error rolls back the whole transaction

    BEGIN TRY
        -----------------------------------------------------------------
        -- 1. Validate input
        -----------------------------------------------------------------
        IF @FromMonth NOT BETWEEN 1 AND 12 OR @ToMonth NOT BETWEEN 1 AND 12
            THROW 50001, 'Month values must be between 1 and 12.', 1;

        IF @FromMonth > @ToMonth
            THROW 50002, '@FromMonth cannot be greater than @ToMonth.', 1;

        IF OBJECT_ID('dbo.ShopSales', 'U') IS NULL
            THROW 50003, 'Table dbo.ShopSales does not exist. Run 10a_LAG_And_LEAD_Simple.sql first.', 1;

        IF NOT EXISTS (SELECT 1 FROM dbo.ShopSales WHERE SaleMonth BETWEEN @FromMonth AND @ToMonth)
            THROW 50004, 'No sales found for the given month range.', 1;

        -----------------------------------------------------------------
        -- 2. Calculate the trend
        --    LAG is calculated over ALL months first, then filtered,
        --    so the first month in the range is still compared with
        --    the month before it.
        -----------------------------------------------------------------
        DECLARE @Result TABLE
        (
            SaleMonth     INT,
            MonthName     VARCHAR(10),
            Amount        INT,
            PreviousMonth INT,
            Trend         VARCHAR(5)
        );

        WITH Trend AS
        (
            SELECT SaleMonth,
                   MonthName,
                   Amount,
                   LAG(Amount) OVER (ORDER BY SaleMonth) AS PreviousMonth
            FROM dbo.ShopSales
        )
        INSERT INTO @Result (SaleMonth, MonthName, Amount, PreviousMonth, Trend)
        SELECT SaleMonth,
               MonthName,
               Amount,
               PreviousMonth,
               CASE
                   WHEN Amount > PreviousMonth THEN 'UP'
                   WHEN Amount < PreviousMonth THEN 'DOWN'
                   ELSE '-'
               END
        FROM Trend
        WHERE SaleMonth BETWEEN @FromMonth AND @ToMonth;

        -----------------------------------------------------------------
        -- 3. Save to the report table (only when asked) - in a transaction
        -----------------------------------------------------------------
        IF @SaveToReport = 1
        BEGIN
            BEGIN TRANSACTION;

                -- remove old saved rows for these months
                DELETE FROM dbo.ShopSalesTrendReport
                WHERE SaleMonth BETWEEN @FromMonth AND @ToMonth;

                -- save the new rows
                INSERT INTO dbo.ShopSalesTrendReport (SaleMonth, MonthName, Amount, PreviousMonth, Trend)
                SELECT SaleMonth, MonthName, Amount, PreviousMonth, Trend
                FROM @Result;

            COMMIT TRANSACTION;
        END;

        -----------------------------------------------------------------
        -- 4. Return the result to the caller
        -----------------------------------------------------------------
        SELECT MonthName, Amount, Trend
        FROM @Result
        ORDER BY SaleMonth;

        RETURN 0;
    END TRY
    BEGIN CATCH
        -- Undo any unfinished work
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Log the error (after rollback, so the log row is kept)
        INSERT INTO dbo.ProcErrorLog
            (ProcedureName, ErrorNumber, ErrorSeverity, ErrorState, ErrorLine, ErrorMessage, Parameters)
        VALUES
            (ERROR_PROCEDURE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE(),
             CONCAT('@FromMonth=', @FromMonth, ', @ToMonth=', @ToMonth, ', @SaveToReport=', @SaveToReport));

        -- Send the original error back to the caller
        THROW;
    END CATCH;
END;
GO

HOW TO RUN (test calls)

All months (same result as the original query)

EXEC dbo.usp_GetShopSalesTrend;

Only March to June

EXEC dbo.usp_GetShopSalesTrend @FromMonth = 3, @ToMonth = 6;

Calculate and save into the report table

EXEC dbo.usp_GetShopSalesTrend @FromMonth = 1, @ToMonth = 6, @SaveToReport = 1;
SELECT * FROM dbo.ShopSalesTrendReport ORDER BY SaleMonth;

Bad input: error is logged and raised (caught here so the script continues)

BEGIN TRY
    EXEC dbo.usp_GetShopSalesTrend @FromMonth = 8, @ToMonth = 2;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

See the logged errors

SELECT * FROM dbo.ProcErrorLog ORDER BY ErrorLogID DESC;
GO