Stored Procedures — CREATE, ALTER, Parameters and Error Handling

Stored procedures are pre-compiled T-SQL programs stored in the database. They accept parameters, execute multiple statements, handle errors, and return results or status codes. For the BlogApp, stored procedures excel at multi-step operations like publishing a post — where several tables must be updated atomically and the logic benefits from living in the database rather than the application. Well-designed procedures are reusable across multiple applications and APIs connecting to the same database.

Stored Procedure Fundamentals #

-- ── Basic stored procedure ─────────────────────────────────────────────────
CREATE OR ALTER PROCEDURE dbo.usp_GetPublishedPosts
    @Page       INT = 1,
    @PageSize   INT = 10,
    @CategorySlug VARCHAR(100) = NULL,
    @AuthorId   NVARCHAR(450) = NULL
AS
BEGIN
    SET NOCOUNT ON;       -- suppress "X row(s) affected" messages
    SET XACT_ABORT ON;    -- auto-rollback on error

    SELECT
        p.Id, p.Title, p.Slug, p.Excerpt,
        p.ViewCount, p.PublishedAt, p.IsFeatured,
        u.DisplayName AS AuthorName,
        c.Name        AS CategoryName,
        COUNT(*) OVER() AS TotalCount
    FROM   dbo.Posts p
    JOIN   dbo.Users      u  ON u.Id  = p.AuthorId
    LEFT   JOIN dbo.Categories c  ON c.Id  = p.CategoryId
    WHERE  p.IsPublished = 1
      AND  (@CategorySlug IS NULL OR c.Slug = @CategorySlug)
      AND  (@AuthorId     IS NULL OR p.AuthorId = @AuthorId)
    ORDER  BY p.PublishedAt DESC, p.Id DESC
    OFFSET (@Page - 1) * @PageSize ROWS
    FETCH  NEXT @PageSize ROWS ONLY;
END;
GO

-- ── Publish post procedure — multi-step transactional ─────────────────────
CREATE OR ALTER PROCEDURE dbo.usp_PublishPost
    @PostId     INT,
    @PublishedBy NVARCHAR(450),
    @NewPostId  INT OUTPUT   -- output parameter: returns the post ID
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Validate post exists and is in review status
        IF NOT EXISTS (
            SELECT 1 FROM dbo.Posts
            WHERE Id = @PostId AND AuthorId = @PublishedBy AND Status = 'review'
        )
        BEGIN
            RAISERROR('Post not found or not in review status.', 16, 1);
            RETURN -1;  -- error status code
        END

        -- Update the post
        UPDATE dbo.Posts
        SET    IsPublished  = 1,
               Status       = 'published',
               PublishedAt  = SYSUTCDATETIME(),
               UpdatedAt    = SYSUTCDATETIME()
        WHERE  Id = @PostId;

        -- Update author's published post count
        UPDATE dbo.Users
        SET    PublishedPostCount = PublishedPostCount + 1
        WHERE  Id = @PublishedBy;

        SET @NewPostId = @PostId;  -- set the OUTPUT parameter

        COMMIT TRANSACTION;
        RETURN 0;  -- success

    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        DECLARE @Msg NVARCHAR(4000) = ERROR_MESSAGE();
        RAISERROR(@Msg, 16, 1);
        RETURN -1;
    END CATCH
END;
GO

-- ── Execute the procedure ──────────────────────────────────────────────────
DECLARE @Result INT;
DECLARE @PublishedId INT;

EXEC @Result = dbo.usp_PublishPost
    @PostId      = 42,
    @PublishedBy = N'user-123',
    @NewPostId   = @PublishedId OUTPUT;

SELECT @Result AS ReturnCode, @PublishedId AS PublishedPostId;

Common Mistakes #

Mistake 1 — Missing SET NOCOUNT ON (row count messages interfere with callers) #

❌ Wrong — procedure without SET NOCOUNT ON; ADO.NET misinterprets row count messages as result sets.

✅ Correct — always add SET NOCOUNT ON as the first line of every stored procedure.

Mistake 2 — RAISERROR without proper severity/state (not caught by application) #

❌ Wrong — RAISERROR('Error', 1, 1); severity 1-10 are informational — not thrown as exceptions by ADO.NET.

✅ Correct — use severity 16+ for application errors: RAISERROR('Error message', 16, 1) or use THROW.