[SQLServer] 프로시저 및 함수 사용법

프로시저 생성

CREATE PROCEDURE [테이블명]
[변수명] [타입],
[변수명] [타입] OUTPUT -- 리턴일경우 OUTPUT 추가
AS
BEGIN
[내용]
END

GO

프로시저 수정

ALTER PROCEDURE [테이블명]
[변수명] [타입],
[변수명] [타입] OUTPUT -- 리턴일경우 OUTPUT 추가
AS
BEGIN
[내용]
END

GO

프로시저 삭제

DROP PROCEDURE IF EXISTS [테이블명]

프로시저 실행

EXEC or EXECUTE [프로시저명] [파라미터]...

함수생성

CREATE FUNCTION [FUNC_NAME] (
[변수명] [타입],
[변수명] [타입]
)
    RETURNS [리턴타입]
AS
BEGIN
  DECLARE [리턴 변수명] [타입]

  [내용]

    RETURN [리턴변수명];
END
go

함수삭제

DROP FUNCTION IF EXISTS [FUNC_NAME]

위의 대괄호 이름들은 작성 형식을 보여 주는 자리표시자다. 실제 테이블명이나 자료형을 넣지 않으면 실행되지 않는다. 프로시저 이름도 테이블명과 같을 필요가 없다.

값 하나를 반환하는 프로시저 예제

CREATE OR ALTER PROCEDURE dbo.CountOrders
    @CustomerId int,
    @OrderCount int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @OrderCount = COUNT(*)
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

DECLARE @count int;
EXEC dbo.CountOrders @CustomerId = 10, @OrderCount = @count OUTPUT;
SELECT @count AS OrderCount;

OUTPUT 매개변수는 호출하는 쪽에서도 OUTPUT을 붙여야 값을 받을 수 있다. 이 예제는 dbo.Orders(CustomerId) 테이블이 있다는 가정 아래 작성했다. CREATE OR ALTER 지원 여부는 사용 중인 SQL Server 버전에서 확인한다.

함수는 반환 형식과 RETURN이 필요하며, SQL 문장 안에서 호출할 수 있는 형태도 있다. 프로시저와 함수는 허용되는 부작용과 호출 방식이 다르다. 단순 조회를 위해 저장 객체를 만들기 전에 일반 SELECT로 충분한지 검토한다. 실제 운영 데이터에 적용할 때는 권한과 트랜잭션 범위를 먼저 확인한다.

프로시저와 함수의 반환 방식

프로시저는 EXEC로 호출해 결과 집합, OUTPUT 매개변수, 정수 반환 코드를 제공할 수 있다. 함수는 스칼라 값 또는 테이블을 반환하고 SQL 식 안에서 호출할 수 있다. 둘의 선택은 단순히 “결과가 있나”가 아니라 호출 위치와 허용되는 작업으로 결정한다. SQL Server 사용자 정의 함수에는 데이터 변경 등 부작용 관련 제약이 있으므로 업데이트 작업을 함수에 넣는 설계는 피한다.

필요	일반적인 선택	호출 형태
여러 SQL 작업과 명시적 결과 집합	저장 프로시저	EXEC dbo.GetOrders @CustomerId = 10
단일 값 계산	스칼라 함수	SELECT dbo.FormatCode(...)
조회 가능한 행 집합 반환	테이블 값 함수	SELECT * FROM dbo.ActiveOrders(...)

다음 예시는 실제 테이블 없이 실행할 수 있는 스칼라 함수다. GO는 SSMS·sqlcmd 등 클라이언트가 배치를 나누는 표기이며 SQL Server 엔진에 보내는 T-SQL 문장 자체는 아니다.

CREATE OR ALTER FUNCTION dbo.AddTax (@Amount decimal(12, 2))
RETURNS decimal(12, 2)
AS
BEGIN
    RETURN ROUND(@Amount * 1.10, 2);
END;
GO

SELECT dbo.AddTax(100.00) AS TotalWithTax;
-- 기대 값: 110.00

세율 10%는 동작 설명을 위한 값이다. 실제 세율과 반올림 규칙을 함수에 하드코딩하면 정책 변경과 지역별 과세에 대응하기 어렵다. 프로덕션에서는 업무 규칙의 소유 위치를 먼저 정한다. 또 스칼라 함수를 많은 행에 적용하면 버전·쿼리 계획에 따라 성능 비용이 생길 수 있으므로 실행 계획과 실제 처리 시간을 확인한다.

OUTPUT과 반환 코드 구분하기

앞의 CountOrders 프로시저에서 @OrderCount OUTPUT은 계산 결과를 호출자에게 건넨다. 호출자도 @count OUTPUT이라고 적어야 받는다. 프로시저의 RETURN은 정수 상태 코드이며 일반적인 조회 결과 집합이나 OUTPUT의 대용품이 아니다. 호출 절차를 도식화하면 다음과 같다.

flowchart LR
  C[호출자 변수 선언] --> P[EXEC 프로시저와 입력 전달]
  P --> Q[SQL 실행]
  Q --> O[OUTPUT 값과 결과 집합 수신]
  O --> V[호출자에서 값 확인]

업무 오류를 보고할 때는 단순 숫자 반환보다 의미 있는 오류 번호와 메시지를 가진 THROW를 사용할 수 있다. 여러 변경을 한 단위로 묶어야 한다면 트랜잭션의 시작·커밋·롤백 책임이 호출자와 프로시저 중 어디에 있는지 명확히 정한다. TRY...CATCH에서 오류를 잡았더라도 조용히 삼키면 호출자가 실패를 성공으로 오해할 수 있다. 테이블을 변경하는 프로시저는 운영 데이터에 적용하기 전에 테스트 DB에서 권한과 롤백 동작을 검증한다.

변경·삭제 전에 의존성 확인하기

CREATE OR ALTER는 해당 객체가 있으면 정의를 수정하고 없으면 만든다. 이전 버전을 포함한 모든 SQL Server 환경에서 지원되는지 확인해야 한다. DROP PROCEDURE IF EXISTS는 정의 자체를 삭제한다. 삭제하기 전에는 호출하는 애플리케이션, 작업 스케줄러, 다른 저장 객체의 의존성을 찾고 배포 순서를 정한다. 프로시저가 존재하지만 실행이 안 된다면 스키마 이름(dbo)과 EXECUTE 권한, 매개변수 이름·타입부터 확인한다. 객체 정의를 바꾼 뒤에는 단순히 생성 성공을 보는 데 그치지 않고 대표 입력과 경계 입력으로 결과를 검사한다.

참고: Microsoft CREATE PROCEDURE, 사용자 정의 함수, THROW.