SP, Function, Exception Log

들어가며

SQL Server를 사용하는 실무 환경에서 Stored Procedure(SP), Function, Exception Log 는 거의 필수적으로 사용된다. 단순히 SELECT 쿼리만 날리는 것과 이 세 가지를 제대로 활용하는 것은 완전히 다른 차원의 개발이다.

이 글에서는 실무에서 배운 각각의 개념부터 사용하는 방식까지 깊게 정리해보려 한다.

1. Stored Procedure (저장 프로시저)

SP란 무엇인가?

Stored Procedure(SP) 는 데이터베이스에 저장된 미리 컴파일된 SQL 문의 집합 이다. 쉽게 말해 자주 사용하는 SQL 작업을 함수처럼 묶어서 저장해둔 것 이다.

일반 쿼리 실행:
[애플리케이션] → SQL 문자열 전송 → [DB] → 파싱 → 컴파일 → 실행

SP 실행:
[애플리케이션] → SP 이름만 전송 → [DB] → 바로 실행 (이미 컴파일됨)

SP를 사용하는 이유

장점	설명
성능 향상	미리 컴파일되어 실행 계획이 캐시됨
보안 강화	테이블 직접 접근 차단, SP 권한만 부여 가능
유지보수 용이	SQL 로직 변경 시 애플리케이션 배포 불필요
네트워크 트래픽 감소	긴 쿼리 대신 SP 이름과 파라미터만 전송
코드 재사용	여러 애플리케이션에서 동일 로직 사용 가능

SP vs Inline SQL, 정말 성능 차이가 있을까?

“SP가 더 빠르다”는 말을 많이 들어봤을 것이다. 하지만 현대 SQL Server(2005 이후)에서는 이 말이 100% 맞지는 않다.

과거 vs 현재

과거 (SQL Server 2000 이전):

• SP만 실행 계획을 캐시했음
• Inline SQL은 매번 컴파일 → SP가 확실히 빠름

현재 (SQL Server 2005 이후):

• 파라미터화된 Inline SQL도 실행 계획을 캐시 함
• 동일한 쿼리 패턴이면 SP와 성능 차이가 거의 없음

-- 이 쿼리도 실행 계획이 캐시됨
SELECT * FROM Users WHERE UserName = @userName AND Password = @password

그럼에도 SP가 유리한 경우

1. 실행 계획 캐시 우선순위

SQL Server의 실행 계획 캐시에는 우선순위가 있다:

우선순위	유형	설명
높음	Compiled (SP)	메모리 압박 시에도 유지됨
중간	Prepared	파라미터화된 쿼리
낮음	Ad-hoc	일반 쿼리, 먼저 삭제됨

메모리 압박 상황에서 SP의 실행 계획이 더 오래 캐시에 남아있다.

2. 네트워크 트래픽 감소

-- Inline SQL: 긴 쿼리 전체를 전송
"SELECT a.Column1, a.Column2, b.Column3 FROM TableA a 
 INNER JOIN TableB b ON a.Id = b.AId WHERE a.Status = @status..."

-- SP 호출: 이름과 파라미터만 전송
"EXEC GetDataByStatus @status = 1"

대용량 트래픽 환경에서는 이 차이가 누적된다.

3. SET NOCOUNT ON 효과

SET NOCOUNT ON  -- "n개 행이 영향받음" 메시지 비활성화

SP에서 이 옵션을 사용하면 불필요한 네트워크 왕복이 줄어든다.

단일 쿼리의 성능 차이는 미미하지만, 대규모 트래픽 환경에서는 SP가 여전히 유리 하다. 특히 MSSQL + ASP.NET 조합에서 SP를 선호하는 이유이다.

벤치마크 결과 요약

여러 벤치마크 테스트 결과를 종합하면:

비교 항목	결과
SP vs 파라미터화된 Inline SQL	거의 동일 (1-5% 차이)
SP vs 비파라미터화된 SQL	SP가 빠름 (매번 컴파일 발생)
SP vs ORM 생성 쿼리	SP가 빠름 (ORM 오버헤드 존재)

결론적으로, 파라미터화된 쿼리를 사용한다면 순수 성능 차이는 크지 않다. 하지만 SP는 성능 외에도 보안, 유지보수, 권한 관리 측면에서 장점이 많다.

SP 기본 문법

생성

CREATE PROCEDURE 스키마명.프로시저명
    @파라미터1     데이터타입
    ,@파라미터2    데이터타입
    ,@출력파라미터  데이터타입    OUTPUT
AS
BEGIN
    -- SQL 로직
END
GO

실행

DECLARE @result INT
EXEC 스키마명.프로시저명 
    @파라미터1      = '값1'
    ,@파라미터2     = '값2'
    ,@출력파라미터  = @result OUTPUT

SELECT @result

삭제

DROP PROCEDURE 스키마명.프로시저명

실무 SP 템플릿

실무에서는 단순히 SQL만 작성하는 것이 아니라, 에러 처리, 트랜잭션 관리, 타임아웃 설정 등을 포함한 표준 템플릿을 사용한다.

1. 헤더 주석

/*!
 * brief   SP 한줄 요약
 * author  작성자, 생성일
 * remarks 보조 설명
 */

누가, 언제, 왜 만들었는지 기록한다. 유지보수 시 필수 정보이다.

2. DROP 후 CREATE 패턴

IF OBJECT_ID('dbo.uspGetBoardList') IS NOT NULL
 BEGIN
    DROP PROCEDURE dbo.uspGetBoardList
 END
GO
CREATE PROCEDURE dbo.uspGetBoardList

SP가 이미 존재하면 삭제 후 새로 생성한다. ALTER 대신 이 패턴을 쓰는 이유는:

• 스크립트를 반복 실행해도 에러가 나지 않음
• 형상 관리(Git 등)에서 전체 코드가 보여 비교가 쉬움

SQL Server 2016 SP1 이상에서는 CREATE OR ALTER PROCEDURE 문법을 사용할 수 있다.

3. SET 옵션들

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 3000
SET XACT_ABORT ON

옵션	설명
SET NOCOUNT ON	“n개 행이 영향받음” 메시지 비활성화. 성능 향상
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	더티 리드 허용. 조회 성능 향상, 락 대기 없음
SET LOCK_TIMEOUT 3000	락 대기 최대 3초. 무한 대기 방지
SET XACT_ABORT ON	에러 발생 시 트랜잭션 자동 롤백

READ UNCOMMITTED 는 조회용 SP에서 주로 사용한다. 데이터 정합성이 중요한 경우에는 사용하지 않는다.

4. TRY-CATCH 구문

BEGIN TRY
    -- 비즈니스 로직
END TRY
BEGIN CATCH
    SET @resultCode = -99
    SET @vResultMsg = ERROR_MESSAGE()
    GOTO LABEL_END
END CATCH

에러 발생 시 CATCH 블록에서 처리한다. ERROR_MESSAGE() 함수로 에러 내용을 가져올 수 있다.

5. LABEL_END 패턴

LABEL_END:
    -- 트랜잭션 처리 또는 정리 작업

GOTO LABEL_END 로 언제든 종료 지점으로 이동할 수 있다. 트랜잭션 COMMIT/ROLLBACK 처리에 유용하다.

동적 쿼리 SP

검색 조건이 가변적인 경우 동적 쿼리 를 사용한다.

동적 쿼리에서 문자열 직접 연결(+ @변수 +)은 SQL Injection 위험이 있다. 반드시 파라미터화된 쿼리를 사용하자.

2. Function (사용자 정의 함수)

Function이란?

Function 은 입력값을 받아 결과값을 반환하는 재사용 가능한 코드 블록 이다. SP와 비슷하지만 중요한 차이가 있다.

SP vs Function 차이

구분	Stored Procedure	Function
반환값	OUTPUT 파라미터, 결과 집합	단일 값 또는 테이블
SELECT 내 사용	불가능	가능
데이터 변경	INSERT/UPDATE/DELETE 가능	불가능 (조회만)
트랜잭션	사용 가능	사용 불가
호출 방식	EXEC	SELECT 절, WHERE 절 등

Function의 종류

1. 스칼라 함수 (Scalar Function)

단일 값 을 반환한다.

-- 반환 타입: INT, VARCHAR, DATETIME 등 단일 값
SELECT dbo.fnGetAge('1990-01-15')  -- 결과: 34

2. 테이블 반환 함수 (Table-Valued Function)

테이블(결과 집합) 을 반환한다.

-- 반환 타입: TABLE
SELECT * FROM dbo.fnGetMemberList(1)  -- 결과: 여러 행

Function 사용 시 주의사항

Function은 SELECT 절, WHERE 절에서 행마다 호출되므로 대용량 데이터에서는 성능 저하가 발생할 수 있다. 복잡한 로직은 SP나 JOIN으로 대체하는 것이 좋다.

-- 느림: 100만 행이면 Function이 100만 번 호출됨
SELECT _memberName, dbo.ufnGetAge(_birthDate)
FROM dbo.TblMember

-- 빠름: 직접 계산
SELECT _memberName
      ,DATEDIFF(YEAR, _birthDate, GETDATE()) AS Age
FROM dbo.TblMember

3. DB Exception Log (예외 로깅)

왜 DB 레벨 로깅이 필요한가?

애플리케이션에서 로깅을 하더라도, DB 내부에서 발생한 에러는 애플리케이션까지 전달되지 않을 수 있다. 특히:

• 트리거에서 발생한 에러
• 백그라운드 잡(Job)에서 발생한 에러
• 복잡한 트랜잭션 중간의 에러

이런 경우를 대비해 DB 자체적으로 에러를 기록 해두면 문제 추적이 훨씬 수월하다.

Exception Log 테이블

/******************************************************************************/
/*!
 * brief   DB Exception Log 테이블
 * author  홍길동, 2024-01-15
 * remarks SP, Function, Trigger 등에서 발생한 예외를 기록
 */
IF OBJECT_ID('dbo.TblExceptionLog') IS NOT NULL
BEGIN
    DROP TABLE dbo.TblExceptionLog
END
GO
CREATE TABLE dbo.TblExceptionLog
(
    _logNo BIGINT IDENTITY(1,1) NOT NULL            -- 고유 번호
    ,_objectName NVARCHAR(100) NOT NULL             -- Exception 발생한 Object 이름
    ,_errorNumber INT NULL                          -- 에러 번호
    ,_errorSeverity INT NULL                        -- 에러 심각도
    ,_errorState INT NULL                           -- 에러 상태
    ,_errorLine INT NULL                            -- 에러 발생 라인
    ,_errorMessage NVARCHAR(4000) NOT NULL          -- 에러 메시지
    ,_parameters NVARCHAR(MAX) NULL                 -- 호출 시 파라미터 값
    ,_registerDate DATETIME NOT NULL                -- 등록 시간
        CONSTRAINT DF_TblExceptionLog_registerDate DEFAULT(GETDATE())
)
GO
ALTER TABLE dbo.TblExceptionLog ADD
    CONSTRAINT PK_TblExceptionLog PRIMARY KEY CLUSTERED
    (
        _logNo ASC
    )
GO

-- 조회용 인덱스
CREATE NONCLUSTERED INDEX IX_TblExceptionLog_objectName 
ON dbo.TblExceptionLog (_objectName, _registerDate DESC)
GO

CREATE NONCLUSTERED INDEX IX_TblExceptionLog_registerDate 
ON dbo.TblExceptionLog (_registerDate DESC)
GO

ERROR 함수들 정리

CATCH 블록 내에서 사용할 수 있는 에러 정보 함수들이다.

함수	설명	예시 값
ERROR_NUMBER()	에러 번호	547, 2627 등
ERROR_MESSAGE()	에러 메시지	‘FK 제약 조건 위반…’
ERROR_SEVERITY()	심각도 (0-25)	16
ERROR_STATE()	상태 코드	1
ERROR_LINE()	에러 발생 라인	42
ERROR_PROCEDURE()	에러 발생 SP 이름	‘uspInsertBoard’

이 함수들은 CATCH 블록 내에서만 유효한 값을 반환한다. CATCH 블록 밖에서는 NULL을 반환한다.

4. Dapper와 SP 조합 (ASP.NET 실무)

Dapper란?

Dapper 는 StackOverflow 팀이 개발한 Micro ORM 이다. Entity Framework 같은 Full ORM과 달리 가볍고 빠르며, SQL에 대한 완전한 제어권 을 유지하면서도 객체 매핑을 자동화해준다.

ADO.NET (순수)      → 빠르지만 코드가 많음
Entity Framework   → 편하지만 오버헤드가 있음
Dapper             → 빠르면서도 코드가 적음 (최적의 균형)

왜 MSSQL + ASP.NET에서 Dapper + SP 조합인가?

1. 성능 벤치마크

다양한 벤치마크 결과를 종합하면:

ORM	상대 성능	특징
ADO.NET	100% (기준)	가장 빠름, 코드 많음
Dapper	95-100%	ADO.NET과 거의 동일
EF Core (AsNoTracking)	70-80%	추적 비활성화 시
EF Core (기본)	50-70%	Change Tracking 오버헤드

Dapper는 ADO.NET의 성능을 거의 유지하면서 코드량을 대폭 줄여준다.

2. SP와의 완벽한 호환

// Entity Framework에서 SP 호출 - 제한적
var result = context.Database.SqlQueryRaw<T>("EXEC MySP @param", param);

// Dapper에서 SP 호출 - 자연스럽고 강력
var result = connection.Query<T>("MySP", param, commandType: CommandType.StoredProcedure);

Dapper는 OUTPUT 파라미터, 다중 결과 집합 등 SP의 모든 기능을 완벽하게 지원 한다.

3. Microsoft 생태계 최적화

MSSQL과 .NET은 같은 Microsoft 제품이다. SP를 사용하면:

• SQL Server의 실행 계획 최적화 를 최대한 활용
• 네이티브 컴파일 SP (In-Memory OLTP) 사용 가능
• .NET의 SqlConnection 과 직접 연동

Dapper 설치

# NuGet Package Manager Console
Install-Package Dapper

# .NET CLI
dotnet add package Dapper

Dapper로 SP 호출하기

기본 조회

using System.Data;
using System.Data.SqlClient;
using Dapper;

public class BoardRepository
{
    private readonly string _connectionString;
    
    public BoardRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    /// <summary>
    /// 게시글 목록 조회
    /// </summary>
    public async Task<IEnumerable<Board>> GetBoardListAsync(int categoryNo, int pageNo, int pageSize)
    {
        using var connection = new SqlConnection(_connectionString);
        
        var parameters = new DynamicParameters();
        parameters.Add("@categoryNo", categoryNo);
        parameters.Add("@pageNo", pageNo);
        parameters.Add("@pageSize", pageSize);
        parameters.Add("@totalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
        parameters.Add("@resultCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
        
        var result = await connection.QueryAsync<Board>(
            "dbo.uspGetBoardList",
            parameters,
            commandType: CommandType.StoredProcedure
        );
        
        // OUTPUT 파라미터 값 가져오기
        int totalCount = parameters.Get<int>("@totalCount");
        int resultCode = parameters.Get<int>("@resultCode");
        
        return result;
    }
}

INSERT/UPDATE/DELETE

/// <summary>
/// 게시글 등록
/// </summary>
public async Task<int> InsertBoardAsync(Board board)
{
    using var connection = new SqlConnection(_connectionString);
    
    var parameters = new DynamicParameters();
    parameters.Add("@categoryNo", board.CategoryNo);
    parameters.Add("@title", board.Title);
    parameters.Add("@content", board.Content);
    parameters.Add("@writerNo", board.WriterNo);
    parameters.Add("@resultCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
    
    await connection.ExecuteAsync(
        "dbo.uspInsertBoard",
        parameters,
        commandType: CommandType.StoredProcedure
    );
    
    return parameters.Get<int>("@resultCode");
}

트랜잭션 처리

/// <summary>
/// 여러 작업을 트랜잭션으로 묶기
/// </summary>
public async Task<bool> ProcessOrderAsync(Order order, List<OrderItem> items)
{
    using var connection = new SqlConnection(_connectionString);
    await connection.OpenAsync();
    
    using var transaction = connection.BeginTransaction();
    
    try
    {
        // 1. 주문 등록
        var orderParams = new DynamicParameters();
        orderParams.Add("@customerId", order.CustomerId);
        orderParams.Add("@orderDate", order.OrderDate);
        orderParams.Add("@orderId", dbType: DbType.Int32, direction: ParameterDirection.Output);
        
        await connection.ExecuteAsync(
            "dbo.uspInsertOrder",
            orderParams,
            transaction: transaction,
            commandType: CommandType.StoredProcedure
        );
        
        int orderId = orderParams.Get<int>("@orderId");
        
        // 2. 주문 상세 등록
        foreach (var item in items)
        {
            var itemParams = new { orderId, item.ProductId, item.Quantity, item.Price };
            await connection.ExecuteAsync(
                "dbo.uspInsertOrderItem",
                itemParams,
                transaction: transaction,
                commandType: CommandType.StoredProcedure
            );
        }
        
        transaction.Commit();
        return true;
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

Dapper vs Entity Framework 선택 기준

상황	추천
SP를 주로 사용하는 프로젝트	Dapper
복잡한 조회 쿼리가 많은 경우	Dapper
성능이 최우선인 경우	Dapper
CRUD가 대부분이고 빠른 개발이 필요한 경우	Entity Framework
마이그레이션, Change Tracking이 필요한 경우	Entity Framework
둘 다 사용	조회는 Dapper, CUD는 EF (하이브리드)

회사에서 SP를 주로 사용한다면 Dapper가 최적의 선택 이다. Entity Framework의 강점인 LINQ, Change Tracking을 제대로 활용하지 못하기 때문이다.

ASP.NET Core에서 Dapper 설정

DI 등록

// Program.cs
builder.Services.AddScoped<IDbConnection>(sp => 
    new SqlConnection(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddScoped<IBoardRepository, BoardRepository>();

Repository 패턴 적용

public interface IBoardRepository
{
    Task<IEnumerable<Board>> GetListAsync(int categoryNo, int pageNo, int pageSize);
    Task<Board?> GetByIdAsync(int boardNo);
    Task<int> InsertAsync(Board board);
    Task<int> UpdateAsync(Board board);
    Task<int> DeleteAsync(int boardNo);
}

public class BoardRepository : IBoardRepository
{
    private readonly IDbConnection _connection;
    
    public BoardRepository(IDbConnection connection)
    {
        _connection = connection;
    }
    
    // 구현...
}

마치며

SQL Server에서 SP, Function, Exception Log, 그리고 Dapper 는 실무 개발의 핵심 요소이다.

SP (Stored Procedure)

• 비즈니스 로직을 DB에 캡슐화
• 성능, 보안, 유지보수 모든 면에서 이점
• 표준 템플릿(SET 옵션, TRY-CATCH, LABEL_END)을 일관되게 사용
• 파라미터화된 Inline SQL과 순수 성능은 비슷하지만, 대규모 환경에서는 SP가 유리

Function

• 재사용 가능한 계산/변환 로직
• SELECT, WHERE 절에서 바로 사용 가능
• 대용량 데이터에서는 성능 주의

Exception Log

• DB 레벨의 에러 추적 시스템
• 문제 발생 시 빠른 원인 파악 가능
• 파라미터까지 기록하면 디버깅이 훨씬 수월

Dapper + SP 조합

• MSSQL + ASP.NET 환경에서 최적의 선택
• ADO.NET 수준의 성능 + 간결한 코드
• SP의 모든 기능(OUTPUT 파라미터, 다중 결과 등) 완벽 지원

이 네 가지를 제대로 활용하면 안정적이고 유지보수하기 쉬운 데이터베이스 시스템을 구축할 수 있다.

---

References

• Dapper 공식 문서
• Dapper GitHub Repository - Performance
• The Big Fight — Dapper vs Entity Framework Detailed Benchmark
• EF Core 9 vs Dapper: Performance Face-Off
• Dapper vs Entity Framework vs ADO.NET Performance Benchmarking
• Entity Framework 8 vs Dapper Benchmark
• Stored Procedures DO NOT increase performance (CodeProject)