SQL Server 프로시저(PROCEDURE) 완벽 가이드 – 생성부터 삭제까지

프로시저(Procedure)는 데이터베이스 안에 저장해 두고 필요할 때마다 재사용할 수 있도록 만든 여러 SQL 문장의 집합입니다. SQL Server에서는 프로시저에 매개변수를 전달할 수 있으며, 함수와 달리 특정 값을 반환하지 않고 대신 실행이 성공했는지 실패했는지만을 알려줍니다.

이 글에서는 SQL Server에서 프로시저를 생성하고 삭제하는 방법을 문법과 실제 예제를 통해 자세히 살펴보겠습니다.

CREATE PROCEDURE – 프로시저 생성하기

문법(Syntax)

SQL Server에서 프로시저를 생성하려면 다음 문법을 사용합니다.

CREATE { PROCEDURE | PROC } [schema_name.]procedure_name
    [@parameter [type_schema_name.] datatype
    [VARYING] [= default] [OUT | OUTPUT | READONLY]
    , @parameter [type_schema_name.] datatype
    [VARYING] [= default] [OUT | OUTPUT | READONLY]]

    [WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause }]
    [FOR REPLICATION]

AS
BEGIN
    [declaration_section]
    executable_section
END;

매개변수 설명:

1. schema_name: 해당 프로시저가 소속된 스키마(schema)의 이름입니다.
2. procedure_name: 생성할 프로시저에 부여할 이름입니다.
3. @parameter: 프로시저에 전달되는 하나 이상의 매개변수입니다.
4. type_schema_name: 매개변수 데이터 타입이 속한 스키마 이름입니다(있는 경우).
5. datatype: @parameter의 데이터 타입입니다.
6. default: @parameter에 할당되는 기본값입니다.
7. OUT / OUTPUT: 해당 매개변수가 출력(output) 매개변수임을 의미합니다.
8. READONLY: 해당 매개변수는 프로시저 내부에서 수정하거나 덮어쓸 수 없습니다.
9. ENCRYPTION: 프로시저의 소스 코드가 시스템에 텍스트 형태로 저장되지 않습니다.
10. RECOMPILE: 해당 프로시저에 대해서는 쿼리가 캐시(cache)되지 않습니다.
11. EXECUTE AS 절: 프로시저를 실행할 보안 컨텍스트(security context)를 지정합니다.
12. FOR REPLICATION: 저장된 프로시저가 복제(replication) 과정 중에만 실행됩니다.

예제

CREATE PROCEDURE spNhanvien
    @nhanvien_name VARCHAR(50) OUT
AS
BEGIN
    DECLARE @nhanvien_id INT;

    SET @nhanvien_id = 8;

    IF @nhanvien_id < 10
        SET @nhanvien_name = 'Smith';
    ELSE
        SET @nhanvien_name = 'Lawrence';
END;

위 예제에서 생성한 프로시저의 이름은 spNhanvien이며, 출력 매개변수인 @nhanvien_name의 결과값은 @nhanvien_id 값에 따라 결정됩니다.

프로시저를 생성한 후에는 아래와 같이 호출하여 사용할 수 있습니다.

USE [test]
GO

DECLARE @site_name VARCHAR(50);

EXEC spNhanvien @site_name OUT;

PRINT @site_name;
GO

출력 매개변수를 받을 변수를 먼저 선언한 뒤, EXEC 키워드로 프로시저를 실행하고 OUT 옵션을 통해 결과값을 전달받는 구조입니다.

DROP PROCEDURE – 프로시저 삭제하기

프로시저를 성공적으로 생성했다 하더라도, 로직 변경이나 불필요한 객체 정리 등의 이유로 데이터베이스에서 프로시저를 제거해야 하는 경우가 있습니다.

문법(Syntax)

프로시저를 삭제하려면 다음 문법을 사용합니다.

DROP PROCEDURE procedure_name;

매개변수 설명:

procedure_name: 삭제하고자 하는 프로시저의 이름입니다.

예제

DROP PROCEDURE spNhanvien;

위 명령을 실행하면 데이터베이스에서 spNhanvien 프로시저가 즉시 삭제됩니다. 삭제 작업은 되돌릴 수 없으므로, 명령을 실행하기 전에 해당 프로시저가 더 이상 다른 곳에서 참조되지 않는지 반드시 확인하는 것이 좋습니다.