오류 처리

BEGIN TRAN;

-- 없는 글에 댓글을 답니다. 외래 키에 막힙니다(3.3).
INSERT INTO Board.COMMENTS (post_num, user_num, content, reg_date)
VALUES (99999, 1, N'없는 글에 댓글', '2026-03-01');

PRINT N'>> 이 줄이 실행됩니까?';

INSERT INTO Board.COMMENTS (post_num, user_num, content, reg_date)
VALUES (1, 1, N'정상 댓글', '2026-03-01');

SELECT COUNT(*) AS 들어간댓글 FROM Board.COMMENTS
WHERE content IN (N'없는 글에 댓글', N'정상 댓글');

ROLLBACK;

BEGIN TRAN;
SELECT num, sort_no FROM Board.POSTS WHERE group_num = 6 ORDER BY sort_no;

-- 99999 번 회원은 없습니다.
EXEC Board.P_POST_REPLY @parent_num = 6, @user_num = 99999, @title = N'x';

SELECT num, sort_no FROM Board.POSTS WHERE group_num = 6 ORDER BY sort_no;
ROLLBACK;

BEGIN TRY
    INSERT INTO Board.COMMENTS (post_num, user_num, content, reg_date)
    VALUES (99999, 1, N'없는 글에 댓글', '2026-03-01');

    PRINT N'>> 이 줄은 실행되지 않습니다';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS 번호, ERROR_SEVERITY() AS 심각도,
           ERROR_STATE() AS 상태, ERROR_LINE() AS 줄,
           ISNULL(ERROR_PROCEDURE(), N'(배치)') AS 프로시저,
           ERROR_MESSAGE() AS 메시지;
END CATCH

-- (가) 같은 배치 안의 이름 오타
BEGIN TRY
    SELECT * FROM Board.POSTZ;
END TRY
BEGIN CATCH
    PRINT N'>> CATCH 에 들어왔습니다';
END CATCH
GO
-- (나) 프로시저 안의 같은 오타
CREATE OR ALTER PROCEDURE Board.P_TYPO AS
BEGIN SET NOCOUNT ON; SELECT * FROM Board.POSTZ; END
GO
BEGIN TRY
    EXEC Board.P_TYPO;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS 번호, ERROR_PROCEDURE() AS 프로시저;
END CATCH

THROW 50001, N'답글을 달 원글을 찾지 못했습니다', 1;
--    번호      메시지                              상태

BEGIN TRY
    INSERT INTO Board.COMMENTS (post_num, user_num, content, reg_date)
    VALUES (99999, 1, N'x', '2026-03-01');
END TRY
BEGIN CATCH
    PRINT N'>> 기록을 남기고 다시 던집니다';
    THROW;                       -- 인수를 적지 않습니다
END CATCH

THROW 50001, N'없는 글에는 답글을 달 수 없습니다', 1;   -- (가)
THROW 50001, N'값 %d 가 잘못되었습니다', 1;             -- (나)

-- (다) 값을 끼우려면 변수로 조립합니다.
DECLARE @m nvarchar(200) = N'글 번호 ' + CAST(99999 AS nvarchar(10)) + N' 를 찾지 못했습니다';
THROW 50001, @m, 1;

-- (가) 기본값입니다.
SET XACT_ABORT OFF;
BEGIN TRAN;
INSERT INTO Board.COMMENTS (post_num, user_num, content, reg_date) VALUES (99999, 1, N'x', '2026-03-01');
SELECT @@TRANCOUNT AS 트랜잭션수, XACT_STATE() AS 상태;
IF @@TRANCOUNT > 0 ROLLBACK;
GO
-- (나) 켜고 같은 일을 합니다.
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
    INSERT INTO Board.COMMENTS (post_num, user_num, content, reg_date) VALUES (99999, 1, N'x', '2026-03-01');
    COMMIT;
END TRY
BEGIN CATCH
    SELECT @@TRANCOUNT AS 트랜잭션수, XACT_STATE() AS 상태, ERROR_NUMBER() AS 번호;
    IF XACT_STATE() <> 0 ROLLBACK;
END CATCH

CREATE OR ALTER PROCEDURE Board.P_POST_REPLY
    @parent_num int,
    @user_num   int,
    @title      nvarchar(200)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        IF NOT EXISTS (SELECT 1 FROM Board.POSTS WHERE num = @parent_num)
            THROW 50001, N'답글을 달 원글을 찾지 못했습니다', 1;

        DECLARE @grp int, @dep smallint, @sort int;

        BEGIN TRAN;

        SELECT @grp = group_num, @dep = depth + 1, @sort = sort_no + 1
        FROM Board.POSTS WHERE num = @parent_num;

        UPDATE Board.POSTS SET sort_no = sort_no + 1
        WHERE group_num = @grp AND sort_no >= @sort;

        INSERT INTO Board.POSTS (board_num, category_num, user_num, parent_num,
                                 group_num, depth, sort_no, title, hit_count, reg_date)
        SELECT board_num, category_num, @user_num, @parent_num, @grp, @dep, @sort,
               @title, 0, '2026-03-01'
        FROM Board.POSTS WHERE num = @parent_num;

        COMMIT;

        SELECT SCOPE_IDENTITY() AS 새글번호;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH
END