dev.syw

오류 처리, 동적 SQL 안전성, 집합 기반 설계와 프로시저 테스트·디버깅 기법을 익힙니다.

고급 T-SQL 프로그래밍 패턴과 테스트·디버깅

입문편에서 저장 프로시저를 작성하는 기본 문법을 익혔다면, 이제는 그 프로시저가 실제 운영 환경에서 어떻게 버텨야 하는지를 다룰 차례입니다. 예상치 못한 오류, 악의적인 입력값, 수백만 건을 처리해야 하는 배치, 그리고 코드가 맞게 동작하는지 검증하는 테스트 자동화까지 — 이 레슨은 T-SQL 프로그래밍의 실전 내공을 한 단계 끌어올리는 데 집중합니다.

동시성·격리 수준, 인덱스 내부 동작은 이미 앞 강에서 다뤘습니다. 이 레슨은 "코드 레벨"의 견고성(robustness) 확보에 초점을 맞춥니다. 잘못된 오류 처리 패턴이 트랜잭션을 조용히 커밋해버리는 함정, 동적 SQL이 SQL 인젝션 통로가 되는 순간, 커서로 짠 10초짜리 로직을 집합 기반으로 0.1초에 끝내는 방법을 실제 코드와 함께 살펴보겠습니다.

학습 목표

  • TRY...CATCH, THROW, XACT_ABORT를 조합하여 트랜잭션 안전성을 보장하는 오류 처리 패턴을 설계할 수 있습니다.
  • sp_executesql 매개변수화를 사용해 동적 SQL에서 SQL 인젝션을 원천 차단할 수 있습니다.
  • 커서 기반 반복 처리 코드를 집합 기반(set-based) 쿼리로 재작성하여 성능을 크게 향상시킬 수 있습니다.
  • 테이블 값 매개변수(TVP) 와 배치 분할 패턴으로 대량 데이터를 효율적으로 처리할 수 있습니다.
  • tSQLt 프레임워크로 저장 프로시저 단위 테스트를 작성하고, PRINT·RAISERROR·실행 계획을 활용한 디버깅 절차를 수행할 수 있습니다.

TRY...CATCH, THROW와 트랜잭션 결합

흔한 함정: 조용한 부분 커밋

오류가 발생했을 때 트랜잭션이 절반만 커밋되는 상황은 데이터 정합성 재앙의 시작입니다. 다음은 잘못된 패턴입니다.

-- ❌ 잘못된 패턴: 오류 발생 후 롤백 없이 함수 종료
CREATE OR ALTER PROCEDURE dbo.TransferBalance
    @FromAccount INT,
    @ToAccount   INT,
    @Amount      DECIMAL(18, 2)
AS
BEGIN
    BEGIN TRANSACTION;

    UPDATE dbo.Accounts SET Balance = Balance - @Amount WHERE AccountId = @FromAccount;
    -- 여기서 오류 발생 시 위 UPDATE는 커밋되지 않지만,
    -- TRY...CATCH 없이 BEGIN TRANSACTION만 있으면 연결 상태에 따라 동작이 달라진다.
    UPDATE dbo.Accounts SET Balance = Balance + @Amount WHERE AccountId = @ToAccount;

    COMMIT TRANSACTION;
END;

올바른 패턴은 TRY...CATCH 블록 안에서 오류를 잡고, CATCH 블록에서 반드시 롤백한 뒤 오류를 재전파하는 것입니다.

-- ✅ 권장 패턴
CREATE OR ALTER PROCEDURE dbo.TransferBalance
    @FromAccount INT,
    @ToAccount   INT,
    @Amount      DECIMAL(18, 2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE dbo.Accounts
        SET    Balance = Balance - @Amount
        WHERE  AccountId = @FromAccount;

        IF @@ROWCOUNT = 0
            THROW 50001, N'출금 계좌를 찾을 수 없습니다.', 1;

        UPDATE dbo.Accounts
        SET    Balance = Balance + @Amount
        WHERE  AccountId = @ToAccount;

        IF @@ROWCOUNT = 0
            THROW 50002, N'입금 계좌를 찾을 수 없습니다.', 1;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- 호출자에게 원래 오류 정보를 그대로 전달
        THROW;
    END CATCH;
END;

⚠️ 주의 RAISERROR는 오류를 재전파하지만 원래 오류 번호·심각도를 유지하지 못합니다. SQL Server 2012 이후에는 THROW;(인자 없는 형태)로 원래 오류를 그대로 재발생시키는 것이 표준입니다.

XACT_ABORT: 명시적 CATCH 없이도 자동 롤백

SET XACT_ABORT ON을 설정하면 런타임 오류가 발생하는 즉시 현재 트랜잭션 전체가 자동으로 롤백됩니다. TRY...CATCH와 함께 쓸 때는 CATCH 진입 시점에 이미 트랜잭션이 롤백 불가 상태(XACT_STATE() = -1)일 수 있으므로 XACT_STATE 확인이 필수입니다.

CREATE OR ALTER PROCEDURE dbo.BulkInsertOrders
    @OrderData NVARCHAR(MAX) -- JSON 예시
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- 런타임 오류 시 즉시 트랜잭션 중단

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO dbo.Orders (CustomerId, OrderDate, TotalAmount)
        SELECT
            JSON_VALUE(item, '$.customerId'),
            CAST(JSON_VALUE(item, '$.orderDate') AS DATE),
            CAST(JSON_VALUE(item, '$.totalAmount') AS DECIMAL(18,2))
        FROM OPENJSON(@OrderData);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- XACT_ABORT ON 상태에서는 이미 트랜잭션이 종료됐을 수 있음
        IF XACT_STATE() = -1
            ROLLBACK TRANSACTION;   -- 커밋 불가능 → 강제 롤백
        ELSE IF XACT_STATE() = 1
            ROLLBACK TRANSACTION;   -- 아직 활성 상태 → 명시 롤백

        DECLARE @Msg NVARCHAR(2048) =
            CONCAT(N'오류 ', ERROR_NUMBER(), N': ', ERROR_MESSAGE(),
                   N' (라인 ', ERROR_LINE(), N')');
        THROW 50100, @Msg, 1;
    END CATCH;
END;
XACT_STATE() 반환값의미권장 조치
1활성 트랜잭션, 커밋 가능COMMIT 또는 ROLLBACK 선택 가능
-1활성 트랜잭션, 커밋 불가(도저히 복구 불가 오류)ROLLBACK만 가능
0활성 트랜잭션 없음아무 조치 불필요

안전한 동적 SQL: sp_executesql 매개변수화

동적 SQL은 강력하지만 잘못 작성하면 SQL 인젝션의 관문이 됩니다. EXEC(@sql) 대신 sp_executesql로 매개변수를 바인딩하는 것이 핵심입니다.

-- ❌ 위험한 패턴: 문자열 연결로 동적 SQL 구성
CREATE OR ALTER PROCEDURE dbo.SearchProducts_Unsafe
    @CategoryName NVARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    -- 입력값이 '; DROP TABLE dbo.Products; --' 이면 테이블이 삭제됨
    SET @sql = N'SELECT * FROM dbo.Products WHERE CategoryName = ''' + @CategoryName + N'''';
    EXEC(@sql);
END;

-- ✅ 안전한 패턴: sp_executesql 매개변수화
CREATE OR ALTER PROCEDURE dbo.SearchProducts_Safe
    @CategoryName NVARCHAR(100),
    @SortColumn   NVARCHAR(50) = N'ProductName'
AS
BEGIN
    SET NOCOUNT ON;

    -- 정렬 컬럼은 값이 아니라 식별자이므로 화이트리스트로 검증
    IF @SortColumn NOT IN (N'ProductName', N'Price', N'CreatedAt')
        THROW 50010, N'허용되지 않은 정렬 컬럼입니다.', 1;

    DECLARE @sql NVARCHAR(MAX);
    DECLARE @params NVARCHAR(500);

    SET @sql = N'
        SELECT ProductId, ProductName, Price, CategoryName
        FROM   dbo.Products
        WHERE  CategoryName = @cat
        ORDER BY ' + QUOTENAME(@SortColumn) + N';';  -- 식별자는 QUOTENAME으로 래핑

    SET @params = N'@cat NVARCHAR(100)';

    EXEC sp_executesql @sql, @params, @cat = @CategoryName;
END;

💡 TIP 매개변수 값은 sp_executesql의 바인딩으로 처리하고, 컬럼명·테이블명 같은 식별자는 반드시 화이트리스트 검증 후 QUOTENAME()으로 감싸는 두 단계를 지키면 인젝션을 원천 차단할 수 있습니다.

동적 SQL 캐시 활용

동적 SQL도 실행 계획 캐시를 재사용할 수 있습니다. EXEC(@sql) 역시 쿼리 텍스트가 완전히 동일하면 캐시된 계획을 재사용하지만, 매개변수화 없이 리터럴 값을 문자열에 인라인하면 값이 바뀔 때마다 쿼리 텍스트가 달라져 별도의 계획이 생성·캐시됩니다. 그 결과 캐시 비대화(plan cache bloat)와 잦은 컴파일 비용이 발생합니다. 반면 sp_executesql로 작성한 쿼리는 매개변수 값이 달라도 동일한 쿼리 텍스트를 유지하므로 하나의 캐시 항목을 공유합니다.

-- 캐시 재사용 여부 확인
SELECT  qs.execution_count,
        qs.total_worker_time / qs.execution_count AS avg_cpu_us,
        SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
            ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE   st.text LIKE N'%dbo.Products%'
ORDER BY qs.execution_count DESC;

커서 대신 집합 기반 설계로 재작성

왜 커서가 느린가

커서는 행을 하나씩 가져와 처리하므로 엔진이 최적화할 여지가 거의 없습니다. 특히 FETCH NEXT 루프는 잠금을 길게 유지하고 I/O를 증폭시킵니다. 집합 기반 쿼리는 옵티마이저가 병렬 실행, 인덱스 스킵 스캔 등을 자유롭게 선택할 수 있습니다.

-- ❌ 커서 기반: 각 직원의 연간 보너스를 계산하여 업데이트
CREATE OR ALTER PROCEDURE dbo.ApplyAnnualBonus_Cursor
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EmpId INT, @Salary DECIMAL(18,2), @BonusRate DECIMAL(5,4);

    DECLARE emp_cursor CURSOR FAST_FORWARD FOR
        SELECT EmployeeId, Salary FROM dbo.Employees;

    OPEN emp_cursor;
    FETCH NEXT FROM emp_cursor INTO @EmpId, @Salary;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @BonusRate = CASE
            WHEN @Salary < 30000  THEN 0.10
            WHEN @Salary < 60000  THEN 0.07
            ELSE                       0.05
        END;

        UPDATE dbo.Employees
        SET    Bonus = @Salary * @BonusRate
        WHERE  EmployeeId = @EmpId;

        FETCH NEXT FROM emp_cursor INTO @EmpId, @Salary;
    END;

    CLOSE emp_cursor;
    DEALLOCATE emp_cursor;
END;

-- ✅ 집합 기반: 단일 UPDATE로 동일한 결과
CREATE OR ALTER PROCEDURE dbo.ApplyAnnualBonus_SetBased
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Employees
    SET    Bonus = Salary * CASE
                                WHEN Salary < 30000 THEN 0.10
                                WHEN Salary < 60000 THEN 0.07
                                ELSE                     0.05
                            END;
END;

백만 건 기준으로 커서 버전은 수십 초, 집합 기반 버전은 수 초 이내로 끝나는 경우가 일반적입니다.

누적 합계·순차 처리도 집합으로

이전 행 값을 참조하는 "누적 계산" 역시 윈도우 함수로 처리할 수 있습니다.

-- 월별 누적 매출 계산 (커서 없이)
SELECT
    SaleMonth,
    MonthlySales,
    SUM(MonthlySales) OVER (ORDER BY SaleMonth
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeSales
FROM dbo.MonthlySalesReport
ORDER BY SaleMonth;

⚠️ 주의 집합 기반으로 바꾸기 어려운 경우(복잡한 분기, 외부 API 호출 등)에는 커서 대신 WHILE + 배치 처리나 TVP 패턴을 검토하세요. 무조건 커서를 없애는 것이 목표가 아니라, 불필요한 행별 반복을 제거하는 것이 목표입니다.

테이블 값 매개변수(TVP)와 배치 분할 패턴

TVP: 여러 행을 하나의 매개변수로

애플리케이션에서 수백~수천 건의 데이터를 저장 프로시저로 한 번에 넘겨야 할 때, TVP(Table-Valued Parameter)를 사용하면 임시 테이블이나 XML 파싱 없이 깔끔하게 처리할 수 있습니다.

-- 1단계: 사용자 정의 테이블 타입 생성
CREATE TYPE dbo.OrderLineType AS TABLE
(
    ProductId INT         NOT NULL,
    Quantity  INT         NOT NULL,
    UnitPrice DECIMAL(18, 2) NOT NULL
);
GO

-- 2단계: TVP를 받는 저장 프로시저
CREATE OR ALTER PROCEDURE dbo.InsertOrderLines
    @OrderId    INT,
    @OrderLines dbo.OrderLineType READONLY  -- READONLY는 필수
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.OrderLines (OrderId, ProductId, Quantity, UnitPrice, LineTotal)
    SELECT
        @OrderId,
        ol.ProductId,
        ol.Quantity,
        ol.UnitPrice,
        ol.Quantity * ol.UnitPrice
    FROM @OrderLines AS ol;
END;
GO

-- 호출 예시 (T-SQL에서 직접 테스트)
DECLARE @Lines dbo.OrderLineType;

INSERT INTO @Lines (ProductId, Quantity, UnitPrice)
VALUES (101, 2, 15000.00),
       (205, 5,  8500.00),
       (310, 1, 42000.00);

EXEC dbo.InsertOrderLines @OrderId = 9001, @OrderLines = @Lines;

💡 TIP .NET 애플리케이션에서는 SqlParameter의 SqlDbType.Structured와 DataTable을 결합하면 TVP를 손쉽게 전달할 수 있습니다. TVP는 네트워크 왕복을 줄이고 파싱 비용도 없애줍니다.

대량 처리 배치 분할 패턴

수백만 건을 한 번에 DELETE/UPDATE하면 트랜잭션 로그가 폭증하고 잠금 경합이 극심해집니다. 일정 크기(배치)로 나눠 처리하는 패턴이 운영 환경의 표준입니다.

-- 90일 이상 된 로그 레코드를 배치로 삭제
CREATE OR ALTER PROCEDURE dbo.PurgeOldLogs
    @BatchSize     INT = 5000,
    @CutoffDate    DATE = NULL,
    @MaxIterations INT = 1000  -- 무한 루프 방지용 안전장치
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @CutoffDate IS NULL
        SET @CutoffDate = DATEADD(DAY, -90, CAST(GETDATE() AS DATE));

    DECLARE @Deleted   INT = 1;
    DECLARE @Total     INT = 0;
    DECLARE @Iteration INT = 0;

    WHILE @Deleted > 0 AND @Iteration < @MaxIterations
    BEGIN
        DELETE TOP (@BatchSize)
        FROM   dbo.ApplicationLogs
        WHERE  CreatedAt < @CutoffDate;

        SET @Deleted   = @@ROWCOUNT;
        SET @Total    += @Deleted;
        SET @Iteration += 1;

        -- 배치 간 짧은 대기로 다른 세션에 잠금 양보 (필요 시)
        IF @Deleted = @BatchSize
            WAITFOR DELAY '00:00:00.100';
    END;

    SELECT @Total AS DeletedRows, @Iteration AS Iterations;
END;
접근 방식장점단점
단일 대형 DELETE코드 단순로그 폭증, 잠금 경합, 롤백 시 오래 걸림
배치 분할 DELETE로그 분산, 잠금 짧게 유지코드 약간 복잡, 총 소요 시간 증가 가능
파티션 전환가장 빠름(메타데이터만 변경)파티션 설계 사전 필요

tSQLt로 저장 프로시저 단위 테스트

tSQLt는 T-SQL 자체로 작성하는 오픈소스 단위 테스트 프레임워크입니다. 테스트 클래스(스키마)와 테스트 프로시저(이름이 test로 시작)로 구성됩니다.

tSQLt 설치 및 기본 구조

-- tSQLt.class 설치 후 (https://tsqlt.org 에서 .dacpac 또는 .sql 다운로드)
-- CLR 활성화 필요: EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

-- 테스트 클래스 생성 (스키마가 곧 클래스)
EXEC tSQLt.NewTestClass 'TransferBalanceTests';
GO

격리(FakeTable)와 AssertEquals 활용

CREATE OR ALTER PROCEDURE TransferBalanceTests.[test 정상 이체 시 잔액이 올바르게 변경된다]
AS
BEGIN
    -- Arrange: 실제 테이블 대신 격리된 가짜 테이블 사용
    EXEC tSQLt.FakeTable 'dbo.Accounts';

    INSERT INTO dbo.Accounts (AccountId, Balance)
    VALUES (1, 100000.00),
           (2,  50000.00);

    -- Act
    EXEC dbo.TransferBalance
        @FromAccount = 1,
        @ToAccount   = 2,
        @Amount      = 30000.00;

    -- Assert: 기대값 테이블과 실제 테이블을 비교
    DECLARE @Expected TABLE (AccountId INT, Balance DECIMAL(18,2));
    INSERT INTO @Expected VALUES (1, 70000.00), (2, 80000.00);

    EXEC tSQLt.AssertEqualsTable '@Expected', 'dbo.Accounts';
END;
GO

-- 오류 케이스 테스트
CREATE OR ALTER PROCEDURE TransferBalanceTests.[test 존재하지 않는 계좌로 이체 시 오류가 발생한다]
AS
BEGIN
    EXEC tSQLt.FakeTable 'dbo.Accounts';
    INSERT INTO dbo.Accounts (AccountId, Balance) VALUES (1, 100000.00);

    -- THROW 50002가 발생하는지 확인
    EXEC tSQLt.ExpectException @ExpectedMessagePattern = '%입금 계좌%';

    EXEC dbo.TransferBalance
        @FromAccount = 1,
        @ToAccount   = 999,  -- 존재하지 않는 계좌
        @Amount      = 10000.00;
END;
GO

-- 모든 테스트 실행
EXEC tSQLt.RunAll;
-- 특정 클래스만 실행
EXEC tSQLt.Run 'TransferBalanceTests';

💡 TIP CI/CD 파이프라인에서는 tSQLt.RunAll의 결과를 XML 형식으로 받아 JUnit 리포트로 변환할 수 있습니다. EXEC tSQLt.RunAll; SELECT * FROM tSQLt.TestResult;로 결과를 조회하세요.

PRINT·RAISERROR·실행 계획을 활용한 디버깅 절차

PRINT와 RAISERROR로 중간값 추적

PRINT는 SQL Server Management Studio(SSMS)의 Messages 탭에 즉시 출력됩니다. 단, 배치가 끝나기 전까지 출력이 버퍼에 쌓이는 경우가 있어 실시간 진행 상황 확인에는 RAISERROR ... WITH NOWAIT를 사용합니다.

CREATE OR ALTER PROCEDURE dbo.DiagnosticDemo
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Step INT = 0;

    SET @Step = 1;
    RAISERROR(N'[Step %d] 처리 시작', 0, 1, @Step) WITH NOWAIT;

    -- 처리 로직 ...
    WAITFOR DELAY '00:00:01';  -- 시뮬레이션

    SET @Step = 2;
    RAISERROR(N'[Step %d] 집계 완료, 영향 행: %d', 0, 1, @Step, @@ROWCOUNT) WITH NOWAIT;

    -- 변수 값 스냅샷 출력
    DECLARE @DebugInfo NVARCHAR(500) =
        CONCAT(N'SessionId=', @@SPID,
               N', TranCount=', @@TRANCOUNT,
               N', Time=', CONVERT(NVARCHAR(30), GETDATE(), 121));
    PRINT @DebugInfo;
END;

실행 계획으로 병목 찾기

코드가 느릴 때는 실제 실행 계획(Actual Execution Plan)을 켜고 실행하면 비용이 높은 연산자(Sort, Hash Match, Table Scan 등)를 즉시 파악할 수 있습니다. 자동화 환경에서는 DMV를 사용합니다.

-- 특정 프로시저의 캐시된 실행 계획 조회
SELECT  qs.execution_count,
        qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us,
        qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
        CAST(qp.query_plan AS NVARCHAR(MAX)) AS plan_xml
FROM    sys.dm_exec_procedure_stats AS ps
JOIN    sys.dm_exec_query_stats     AS qs
        ON  ps.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE   OBJECT_NAME(ps.object_id) = 'TransferBalance'
ORDER BY qs.total_elapsed_time DESC;

디버깅 체크리스트

  1. PRINT / RAISERROR WITH NOWAIT — 단계별 변수 값과 진행 상황 확인
  2. SET STATISTICS IO, TIME ON — 논리적 읽기 횟수와 CPU/경과 시간 측정
  3. 실제 실행 계획 — 예상 행 수 vs 실제 행 수 차이, 경고(노란 삼각형) 확인
  4. @@ROWCOUNT 검증 — 각 DML 직후 영향 행 수를 검사해 의도치 않은 0건 처리 포착
  5. sys.dm_exec_query_stats — 운영 환경에서 캐시된 계획의 실행 통계 추적
  6. Extended Events — SSMS 디버거 사용이 불가한 운영 서버에서 세션 수준 이벤트 캡처
-- 논리적 읽기·시간 통계 활성화
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

EXEC dbo.TransferBalance @FromAccount = 1, @ToAccount = 2, @Amount = 1000;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
-- Messages 탭에서 "Table 'Accounts'. Scan count N, logical reads M" 형태의 결과 확인
-- (두 계좌를 각각 UPDATE하므로 Accounts 테이블 접근은 여러 줄로 출력될 수 있으며, 구체 수치는 인덱스·실행 계획에 따라 달라집니다)

요약

  • TRY...CATCH + THROW를 사용하면 오류를 원래 정보 그대로 재전파할 수 있으며, CATCH 블록에서 XACT_STATE()를 확인해 트랜잭션 상태에 따라 ROLLBACK을 수행해야 합니다.
  • XACT_ABORT ON은 런타임 오류 발생 시 트랜잭션을 자동 중단하지만, CATCH 블록 내에서 XACT_STATE() = -1 상태를 반드시 처리해야 합니다.
  • 동적 SQL은 sp_executesql 매개변수 바인딩 + 식별자에 대한 QUOTENAME + 화이트리스트 검증으로 SQL 인젝션을 차단합니다.
  • 커서 기반 행별 처리는 집합 기반(set-based) 쿼리로 재작성하면 성능이 극적으로 향상되며, TVP를 사용하면 다건 데이터를 단일 프로시저 호출로 효율적으로 처리할 수 있습니다.
  • 대량 DML은 배치 분할 패턴으로 트랜잭션 로그 폭증과 잠금 경합을 방지합니다.
  • tSQLt의 FakeTable + AssertEqualsTable + ExpectException으로 저장 프로시저를 격리된 환경에서 단위 테스트할 수 있으며, RAISERROR WITH NOWAIT와 SET STATISTICS IO/TIME ON은 실시간 디버깅의 핵심 도구입니다.

연습문제

  1. 다음 요구사항을 만족하는 저장 프로시저 dbo.WithdrawCash를 작성하세요. 출금 계좌 잔액이 부족하면 사용자 정의 오류(번호 50010)를 발생시키고, 정상 처리 시 트랜잭션을 커밋해야 합니다. XACT_ABORT ON과 TRY...CATCH를 모두 사용하세요.

    힌트 XACT_STATE() = -1일 때만 ROLLBACK이 필요할 수 있습니다. 잔액 부족 검사는 IF (SELECT Balance FROM ...) < @Amount 형태로 트랜잭션 내부에서 수행하세요.

  2. 아래 커서 기반 프로시저를 집합 기반 단일 UPDATE 문으로 재작성하세요.

    -- 변환 대상
DECLARE @Id INT, @Score INT;
DECLARE c CURSOR FAST_FORWARD FOR
    SELECT StudentId, ExamScore FROM dbo.Students;
OPEN c;
FETCH NEXT FROM c INTO @Id, @Score;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE dbo.Students
    SET    Grade = CASE WHEN @Score >= 90 THEN 'A'
                        WHEN @Score >= 80 THEN 'B'
                        WHEN @Score >= 70 THEN 'C'
                        ELSE 'F' END
    WHERE  StudentId = @Id;
    FETCH NEXT FROM c INTO @Id, @Score;
END;
CLOSE c; DEALLOCATE c;

    힌트 CASE 식을 UPDATE의 SET 절 안에 직접 사용할 수 있습니다.

  3. dbo.ProductType이라는 TVP 타입(ProductId INT, Quantity INT)과, 해당 TVP를 받아 dbo.Inventory 테이블의 StockCount를 차감하는 저장 프로시저 dbo.DeductInventory를 작성하세요.

    힌트 UPDATE i SET i.StockCount = i.StockCount - t.Quantity FROM dbo.Inventory i JOIN @InputTVP t ON ... 패턴을 활용하세요.

  4. dbo.Accounts 테이블에서 dbo.TransferBalance 프로시저를 호출할 때 존재하지 않는 출금 계좌를 넘기면 오류가 발생하는지 검증하는 tSQLt 테스트 프로시저를 작성하세요.

    힌트 tSQLt.FakeTable로 실제 테이블을 격리하고, tSQLt.ExpectException으로 THROW된 오류 메시지 패턴을 검증하세요.

💡 연습문제 풀이

불러오는 중…

함께 보면 좋은 자료

📑 치트시트

댓글 0

“MSSQL 심화” 강좌에 대한 댓글입니다.

댓글을 작성하려면 로그인이 필요합니다.