실전 — 게시판 프로시저 세트

CREATE OR ALTER PROCEDURE Board.P_POST_LIST
    @board_num    int,
    @category_num int = NULL,          -- 말머리로 거를 때
    @keyword      nvarchar(100) = NULL, -- 제목 검색
    @group_num    int = NULL,          -- 앞 쪽 마지막 행의 키(5.6)
    @sort_no      int = NULL,
    @size         int = 20
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (@size + 1)                  -- 한 건 더 — 다음 쪽 여부(5.6)
        p.num, p.board_num, p.category_num, p.user_num,
        p.group_num, p.depth, p.sort_no,
        p.title, p.hit_count, p.reg_date, p.mod_date,
        u.nickname,
        (SELECT COUNT(*) FROM Board.COMMENTS c
         WHERE c.post_num = p.num) AS comment_count
    FROM Board.POSTS p
        JOIN Member.USERS u ON u.num = p.user_num
    WHERE p.board_num = @board_num
      AND (@category_num IS NULL OR p.category_num = @category_num)
      AND (@keyword IS NULL OR p.title LIKE N'%' + @keyword + N'%')
      AND (@group_num IS NULL                    -- 첫 쪽
           OR (p.group_num <= @group_num           -- 범위를 따로(5.4 · 5.6)
               AND (p.group_num < @group_num OR p.sort_no > @sort_no)))
    ORDER BY p.group_num DESC, p.sort_no
    OPTION (RECOMPILE);                     -- 매개 변수가 여럿이라(5.3)
END

CREATE OR ALTER PROCEDURE Board.P_POST_SELECT
    @num int,
    @hit bit = 1            -- 미리보기에서는 0 으로 부릅니다
AS
BEGIN
    SET NOCOUNT ON;

    IF @hit = 1
        UPDATE Board.POSTS SET hit_count = hit_count + 1 WHERE num = @num;

    -- 첫째 — 글
    SELECT p.num, p.board_num, p.category_num, p.user_num,
           p.parent_num, p.group_num, p.depth, p.sort_no,
           p.title, p.content, p.hit_count, p.reg_date, p.mod_date,
           u.nickname
    FROM Board.POSTS p
        JOIN Member.USERS u ON u.num = p.user_num
    WHERE p.num = @num;

    -- 둘째 — 댓글
    SELECT c.num, c.user_num, c.content, c.reg_date, u.nickname
    FROM Board.COMMENTS c
        JOIN Member.USERS u ON u.num = c.user_num
    WHERE c.post_num = @num
    ORDER BY c.num;
END

await using var r = await cmd.ExecuteReaderAsync();

if (await r.ReadAsync())
    post = ReadPost(r);              // 첫째 — 글

if (await r.NextResultAsync())    // 둘째로 넘어갑니다
    while (await r.ReadAsync())
        comments.Add(ReadComment(r));

CREATE OR ALTER PROCEDURE Board.P_POST_SAVE
    @num          int = NULL,          -- 있으면 고치기
    @board_num    int,
    @category_num int = NULL,
    @user_num     int,
    @parent_num   int = NULL,          -- 있으면 답글
    @title        nvarchar(200),
    @content      nvarchar(max) = NULL,
    @result       int = 0 OUTPUT       -- 글 번호를 돌려줍니다
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;                       -- 4.5

    IF LEN(LTRIM(RTRIM(@title))) = 0
        THROW 50010, N'제목을 입력하십시오.', 1;

    DECLARE @outer bit = CASE WHEN @@TRANCOUNT > 0 THEN 1 ELSE 0 END;  -- 4.6

    BEGIN TRY
        IF @outer = 0 BEGIN TRAN;

        /* 고치기 */
        IF @num IS NOT NULL
        BEGIN
            UPDATE Board.POSTS
            SET category_num = @category_num,
                title        = @title,
                content      = @content,
                mod_date     = SYSDATETIME()
            WHERE num = @num AND user_num = @user_num;   -- 권한을 조건에

            IF @@ROWCOUNT = 0
                THROW 50011, N'글이 없거나 고칠 권한이 없습니다.', 1;

            SET @result = @num;
        END
        /* 새 글 · 답글 */
        ELSE
        BEGIN
            DECLARE @group int, @depth smallint, @sort int;

            IF @parent_num IS NULL
            BEGIN
                SET @depth = 0;  SET @sort = 0;
            END
            ELSE
            BEGIN
                -- UPDLOCK 으로 잡아 두 사람이 같은 자리를 노리지 않게 합니다(5.5).
                SELECT @group = group_num, @depth = depth + 1, @sort = sort_no + 1
                FROM Board.POSTS WITH (UPDLOCK) WHERE num = @parent_num;

                IF @group IS NULL
                    THROW 50012, N'답글을 달 원글이 없습니다.', 1;

                -- 뒤에 있는 것들을 한 칸씩 밉니다(3.9).
                UPDATE Board.POSTS SET sort_no = sort_no + 1
                WHERE group_num = @group AND sort_no >= @sort;
            END

            INSERT INTO Board.POSTS
                (board_num, category_num, user_num, parent_num,
                 group_num, depth, sort_no, title, content, hit_count, reg_date)
            VALUES (@board_num, @category_num, @user_num, @parent_num,
                 ISNULL(@group, 0), @depth, @sort, @title, @content, 0, SYSDATETIME());

            SET @result = SCOPE_IDENTITY();               -- 1.8

            -- 원글은 제 번호가 덩어리 번호가 됩니다.
            IF @parent_num IS NULL
                UPDATE Board.POSTS SET group_num = @result WHERE num = @result;
        END

        IF @outer = 0 COMMIT;
    END TRY
    BEGIN CATCH
        IF @outer = 0 AND XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH
END

CREATE OR ALTER PROCEDURE Board.P_POST_DELETE
    @num      int,
    @user_num int,
    @result   int = 0 OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @outer bit = CASE WHEN @@TRANCOUNT > 0 THEN 1 ELSE 0 END;

    BEGIN TRY
        IF @outer = 0 BEGIN TRAN;

        DECLARE @owner int, @group int, @sort int;
        SELECT @owner = user_num, @group = group_num, @sort = sort_no
        FROM Board.POSTS WITH (UPDLOCK) WHERE num = @num;

        IF @owner IS NULL
            THROW 50013, N'글이 없습니다.', 1;
        IF @owner <> @user_num
            THROW 50014, N'지울 권한이 없습니다.', 1;
        IF EXISTS (SELECT 1 FROM Board.POSTS WHERE parent_num = @num)
            THROW 50015, N'답글이 달려 있어 지울 수 없습니다.', 1;

        -- 딸린 것부터 지웁니다. 외래 키 순서입니다(3.3).
        DELETE FROM Board.COMMENTS WHERE post_num = @num;
        DELETE FROM Board.FILES    WHERE post_num = @num;
        DELETE FROM Board.POSTS    WHERE num = @num;
        SET @result = @@ROWCOUNT;

        -- 뒤에 있던 것들을 한 칸씩 당깁니다.
        UPDATE Board.POSTS SET sort_no = sort_no - 1
        WHERE group_num = @group AND sort_no > @sort;

        IF @outer = 0 COMMIT;
    END TRY
    BEGIN CATCH
        IF @outer = 0 AND XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH
END

BEGIN TRAN;

BEGIN TRY
    EXEC Board.P_POST_DELETE @num = @p1, @user_num = 1;   -- 50015 로 실패
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();                                -- 잡았습니다
END CATCH

EXEC Board.P_POST_DELETE @num = @p3, @user_num = 3;       -- 이번엔 될 줄 알았는데

-- 바깥에서 잡았다면 상태를 확인하고 롤백해야 합니다(4.6).
BEGIN CATCH
    PRINT ERROR_MESSAGE();
    IF XACT_STATE() = -1 ROLLBACK;      -- -1 이면 되돌리는 수밖에 없습니다
END CATCH