CREATE PROCEDURE(Transact-SQL)

저장 프로시저를 만듭니다. 저장 프로시저는 저장된 Transact-SQL 문의 모음이거나 사용자가 지정한 매개 변수를 사용하고 반환할 수 있는 Microsoft .NET Framework CLR(공용 언어 런타임) 메서드에 대한 참조입니다. 프로시저는 영구적으로 사용하기 위해 만들거나 특정 세션에서 임시로 사용하도록 로컬 임시 프로시저로 만들거나 모든 세션에서 임시로 사용하도록 전역 임시 프로시저로 만듭니다.

SQL Server 인스턴스가 시작될 때 저장 프로시저가 자동으로 실행되도록 만들 수도 있습니다.

Transact-SQL 구문 표기 규칙

구문

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name

인수

• schema_name
  프로시저가 속한 스키마의 이름입니다.

• procedure_name
  새 저장 프로시저의 이름입니다. 프로시저 이름은 식별자에 적용되는 규칙을 준수해야 하며 스키마 내에서 고유해야 합니다.

  프로시저 이름에는 sp_ 접두사를 사용하지 않는 것이 좋습니다. 이 접두사는 SQL Server에서 시스템 저장 프로시저를 지정하는 데 사용됩니다. 자세한 내용은 저장 프로시저 만들기(데이터베이스 엔진)를 참조하십시오.

  로컬 임시 프로시저의 경우 숫자 기호(#) 하나를 procedure_name 앞에 사용(#procedure_name)하고 전역 임시 프로시저의 경우 숫자 기호 두 개를 사용(##procedure_name)하여 로컬 또는 전역 임시 프로시저를 각각 만들 수 있습니다. CLR 저장 프로시저에는 임시 이름을 지정할 수 없습니다.

  저장 프로시저 또는 전역 임시 프로시저의 전체 이름은 ##을 포함하여 128자를 초과할 수 없습니다. 로컬 임시 저장 프로시저의 전체 이름은 #을 포함하여 116자를 초과할 수 없습니다.

• **;**number
  같은 이름의 프로시저를 그룹화하는 데 사용하는 정수입니다(선택 사항). 이렇게 그룹화된 프로시저는 DROP PROCEDURE 문 하나를 사용하여 한꺼번에 삭제할 수 있습니다. 예를 들어 orders라는 응용 프로그램에서 orderproc;1, orderproc;2 등으로 번호가 매겨진 프로시저를 사용할 수 있습니다. DROP PROCEDURE orderproc 문을 실행하면 전체 그룹이 삭제됩니다. 이름에 구분 식별자가 포함될 경우 정수가 식별자에 포함되어서는 안 됩니다. procedure_name에만 적합한 구분자를 사용합니다.

  번호가 매겨진 저장 프로시저에는 다음과 같은 제한 사항이 있습니다.

  • xml 또는 CLR 사용자 정의 형식을 데이터 형식으로 사용할 수 없습니다.

  • 번호가 매겨진 저장 프로시저에 대한 계획 지침을 만들 수 없습니다.

  [!참고]

  Microsoft SQL Server의 이후 버전에서는 이 기능이 제거됩니다. 새 개발 작업에서는 이 기능을 사용하지 않도록 하고, 현재 이 기능을 사용하는 응용 프로그램은 수정하십시오.

• **@**parameter
  프로시저의 매개 변수입니다. CREATE PROCEDURE 문에서 하나 이상의 매개 변수를 선언할 수 있습니다. 선언된 각 매개 변수의 값은 기본값이 정의된 경우나 다른 매개 변수와 값이 같도록 설정된 경우를 제외하면 프로시저가 호출될 때 사용자가 지정해야 합니다. 하나의 저장 프로시저에 최대 2,100개의 매개 변수를 지정할 수 있습니다. 프로시저에 테이블 반환 매개 변수가 포함되어 있고 호출에 매개 변수가 없는 경우 빈 테이블의 기본값이 전달됩니다.

  at 기호(@)를 첫 번째 문자로 사용하여 매개 변수 이름을 지정합니다. 매개 변수 이름은 식별자에 대한 규칙을 따라야 합니다. 매개 변수는 프로시저에서 로컬로 사용되므로 다른 프로시저에서 동일한 매개 변수 이름을 사용할 수 있습니다. 기본적으로 매개 변수는 상수 식 대신 사용될 수 있지만 테이블 이름, 열 이름 또는 다른 데이터베이스 개체 이름 대신 사용될 수는 없습니다. 자세한 내용은 EXECUTE(Transact-SQL)를 참조하십시오.

  FOR REPLICATION을 지정하면 매개 변수를 선언할 수 없습니다.

• [ type_schema_name**.** ] data_type
  매개 변수 및 변수가 속하는 스키마의 데이터 형식입니다. 모든 데이터 형식을 Transact-SQL 저장 프로시저의 매개 변수로 사용할 수 있습니다. 사용자 정의 테이블 형식을 사용하면 테이블 반환 매개 변수를 Transact-SQL 저장 프로시저의 매개 변수로 선언할 수 있습니다. 테이블 반환 매개 변수는 입력 매개 변수로만 지정할 수 있으며 READONLY 키워드와 함께 사용해야 합니다. cursor 데이터 형식은 OUTPUT 매개 변수에만 사용할 수 있습니다. cursor 데이터 형식을 지정하면 VARYING과 OUTPUT 키워드도 지정해야 합니다. 여러 출력 매개 변수를 cursor 데이터 형식으로 지정할 수 있습니다.

  CLR 저장 프로시저의 경우 char, varchar, text, ntext, image, cursor, 사용자 정의 테이블 형식 및 table은 매개 변수로 지정할 수 없습니다. CLR 유형과 SQL Server 시스템 데이터 형식 간의 관계에 대한 자세한 내용은 CLR 매개 변수 데이터 매핑를 참조하십시오. SQL Server 시스템 데이터 형식과 해당 구문에 대한 자세한 내용은 데이터 형식(Transact-SQL)을 참조하십시오.

  매개 변수의 데이터 형식이 CLR 사용자 정의 형식인 경우 해당 유형에 대해 EXECUTE 권한이 있어야 합니다.

  type_schema_name을 지정하지 않으면 SQL Server 데이터베이스 엔진에서는 다음 순서로 type_name을 참조합니다.

  • SQL Server 시스템 데이터 형식

  • 현재 데이터베이스에 있는 현재 사용자의 기본 스키마

  • 현재 데이터베이스의 dbo 스키마

  번호가 매겨진 저장 프로시저의 데이터 형식은 xml 또는 CLR 사용자 정의 형식이 될 수 없습니다.

• VARYING
  지원되는 결과 집합을 출력 매개 변수로 지정합니다. 이 매개 변수는 저장 프로시저에 의해 동적으로 생성될 수 있으며 해당 내용은 여러 가지가 될 수 있습니다. cursor 매개 변수에만 적용됩니다.

• default
  매개 변수의 기본값입니다. default 값이 정의되어 있으면 해당 매개 변수 값을 지정하지 않아도 프로시저를 실행할 수 있습니다. 기본값은 상수이거나 NULL입니다. 프로시저에서 매개 변수에 LIKE 키워드를 사용할 경우 % _ [] 및 [^] 등의 와일드카드 문자가 포함될 수 있습니다.

  [!참고]

  기본값은 CLR 프로시저의 경우에만 sys.parameters.default 열에 기록됩니다. Transact-SQL 프로시저 매개 변수의 경우 이 열은 NULL이 됩니다.

• OUTPUT
  매개 변수가 출력 매개 변수임을 나타냅니다. 이 옵션 값은 EXECUTE 문 호출 시 반환됩니다. OUTPUT 매개 변수를 사용하여 프로시저의 호출자에 값을 반환할 수 있습니다. 프로시저가 CLR 프로시저가 아니면 text, ntext 및 image 매개 변수를 OUTPUT 매개 변수로 사용할 수 없습니다. 프로시저가 CLR 프로시저가 아닐 경우 OUTPUT 키워드를 사용하는 출력 매개 변수가 커서 자리 표시자일 수 있습니다. 사용자 정의 테이블 형식은 저장 프로시저의 출력 매개 변수로 지정할 수 없습니다.

• READONLY
  프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음을 나타냅니다. 매개 변수 유형이 사용자 정의 테이블 형식인 경우 READONLY를 지정해야 합니다.

• RECOMPILE
  데이터베이스 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일됩니다. FOR REPLICATION을 지정하면 이 옵션을 사용할 수 없습니다. CLR 저장 프로시저에는 RECOMPILE을 지정할 수 없습니다.

  데이터베이스 엔진에서 저장 프로시저 안에 있는 개별 쿼리에 대한 계획을 삭제하려면 RECOMPILE 쿼리 힌트를 사용합니다. 자세한 내용은 쿼리 힌트(Transact-SQL)를 참조하십시오. 저장 프로시저에 속하는 쿼리의 하위 집합에서만 예외 값 또는 임시 값을 사용할 경우 RECOMPILE 쿼리 힌트를 사용하십시오.

• ENCRYPTION
  SQL Server에서 CREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환됩니다. 변조된 출력은 SQL Server의 카탈로그 뷰 어디에서도 직접 표시되지 않습니다. 시스템 테이블 또는 데이터베이스 파일에 대한 액세스 권한이 없는 사용자는 변조된 텍스트를 검색할 수 없습니다. 그러나 DAC 포트를 통해 시스템 테이블에 액세스하거나 데이터베이스 파일에 직접 액세스할 수 있는 권한을 가진 사용자는 변조된 텍스트를 사용할 수 있습니다. 또한 디버거를 서버 프로세스에 연결할 수 있는 사용자는 런타임에 메모리에서 해독된 프로시저를 검색할 수 있습니다. 시스템 메타데이터에 액세스하는 방법은 메타데이터 표시 유형 구성를 참조하십시오.

  CLR 저장 프로시저에는 이 옵션이 유효하지 않습니다.

  이 옵션을 사용하여 만든 프로시저는 SQL Server 복제의 일부로 게시할 수 없습니다.

• EXECUTE AS
  저장 프로시저를 실행할 보안 컨텍스트를 지정합니다.

  자세한 내용은 EXECUTE AS 절(Transact-SQL)을 참조하십시오.

• FOR REPLICATION
  복제용으로 만든 저장 프로시저가 구독자에서 실행될 수 없도록 지정합니다. FOR REPLICATION 옵션을 사용하여 만들어진 저장 프로시저는 저장 프로시저 필터로 사용되며 복제 중에만 실행됩니다. FOR REPLICATION을 지정하면 매개 변수를 선언할 수 없습니다. CLR 저장 프로시저에는 FOR REPLICATION을 지정할 수 없습니다. FOR REPLICATION으로 만든 프로시저의 경우 RECOMPILE 옵션이 무시됩니다.

  FOR REPLICATION 프로시저는 sys.objects와 sys.procedures에 RF 개체 유형을 사용합니다.

• <sql_statement>
  프로시저에 포함될 하나 이상의 Transact-SQL 문입니다. 적용되는 몇 가지 제한 사항에 대한 자세한 내용은 주의 섹션을 참조하십시오.

• EXTERNAL NAME assembly_name**.class_name.method_name
  CLR 저장 프로시저가 참조할 .NET Framework 어셈블리의 메서드를 지정합니다. class_name은 유효한 SQL Server 식별자여야 하며 해당 어셈블리에 클래스로 존재해야 합니다. 네임스페이스로 한정된 이름이 있는 경우 클래스 이름은 대괄호([]) 또는 따옴표(""**)를 사용하여 구분되어야 합니다. 지정한 메서드는 해당 클래스의 정적 메서드여야 합니다.

  [!참고]

  기본적으로 SQL Server에서는 CLR 코드를 실행할 수 없습니다. 공용 언어 런타임 모듈을 참조하는 데이터베이스 개체를 생성, 수정 및 삭제할 수 있지만 clr enabled 옵션을 설정할 때까지 SQL Server에서 이러한 참조를 실행할 수 없습니다. 이 옵션을 설정하려면 sp_configure를 사용합니다.

주의

저장 프로시저의 미리 정의된 최대 크기가 없습니다.

사용자 정의 저장 프로시저는 현재 데이터베이스에서만 만들 수 있습니다. 그러나 임시 프로시저는 tempdb에서 항상 생성되므로 예외입니다. 스키마 이름을 지정하지 않으면 프로시저를 만드는 사용자의 기본 스키마가 사용됩니다. 스키마에 대한 자세한 내용은 사용자와 스키마 분리를 참조하십시오.

CREATE PROCEDURE 문은 단일 일괄 처리에서 다른 Transact-SQL 문과 함께 사용할 수 없습니다.

기본적으로 매개 변수는 Null이 허용됩니다. NULL 매개 변수 값이 전달되고, 참조되는 열이 NULL 값을 허용하지 않는 CREATE TABLE 또는 ALTER TABLE 문에 해당 매개 변수가 사용되면 데이터베이스 엔진에 오류가 발생합니다. Null 값을 허용하지 않는 열에 NULL이 전달되지 않게 하려면 프로시저에 프로그램 논리를 추가하거나 CREATE TABLE 또는 ALTER TABLE의 DEFAULT 키워드를 사용하여 열의 기본값을 사용합니다.

임시 테이블의 열마다 NULL 또는 NOT NULL을 명시적으로 지정하는 것이 좋습니다. ANSI_DFLT_ON과 ANSI_DFLT_OFF 옵션은 CREATE TABLE 또는 ALTER TABLE 문에 NULL 또는 NOT NULL 특성이 지정되지 않은 경우 데이터베이스 엔진에서 열에 이러한 특성을 할당하는 방식을 제어합니다. 프로시저를 만든 연결과는 이 옵션 설정이 다른 저장 프로시저를 연결이 실행하면 두 번째 연결에 만들어진 테이블의 열은 Null 허용 여부가 달라질 수 있으며 다른 동작을 나타낼 수 있습니다. 각 열에 NULL 또는 NOT NULL을 명시적으로 지정하면 저장 프로시저를 실행하는 모든 연결에 대해 Null 허용 여부가 동일한 임시 테이블이 만들어집니다.

SET 옵션 사용

데이터베이스 엔진에서는 Transact-SQL 저장 프로시저를 만들거나 수정할 때 SET QUOTED_IDENTIFIER와 SET ANSI_NULLS의 설정을 저장합니다. 저장 프로시저를 실행할 때는 이러한 원래 설정이 사용됩니다. 따라서 저장 프로시저 실행 시 SET QUOTED_IDENTIFIER와 SET ANSI_NULLS에 대한 클라이언트 세션 설정은 모두 무시됩니다. SET ARITHABORT, SET ANSI_WARNINGS, SET ANSI_PADDINGS 등의 기타 SET 옵션은 저장 프로시저를 만들거나 수정할 때 저장되지 않습니다. 저장 프로시저의 논리가 특정 설정에 따라 좌우될 경우 적합한 설정을 위해 프로시저 시작 부분에 SET 문을 포함시킵니다. 저장 프로시저에서 SET 문을 실행할 경우 해당 설정은 저장 프로시저의 실행이 완료될 때까지만 유지됩니다. 그런 다음 저장 프로시저가 호출되었을 때의 값으로 설정이 복원됩니다. 따라서 각 클라이언트는 저장 프로시저의 논리에 영향을 주지 않고 원하는 옵션을 설정할 수 있습니다.

[!참고]

저장 프로시저나 사용자 정의 함수에 매개 변수를 전달할 때 또는 일괄 처리 문에서 변수를 선언하고 설정할 때 ANSI_WARNINGS는 인식되지 않습니다. 예를 들어 변수가 char(3)로 정의된 경우 3자보다 큰 값을 설정하면 정의된 크기로 데이터가 잘리고 INSERT 또는 UPDATE 문이 성공합니다.

CLR 저장 프로시저에 매개 변수 사용

CLR 저장 프로시저의 매개 변수는 스칼라 SQL Server 시스템 데이터 형식 중 하나일 수 있습니다.

데이터베이스 엔진이 .NET Framework에서 오버로드될 때 올바른 메서드를 참조하려면 <method_specifier>의 메서드에 다음과 같은 특징이 있어야 합니다.

• 정적 메서드로 선언되었습니다.

• 프로시저의 매개 변수 개수와 동일한 개수의 매개 변수를 받습니다.

• 해당 클래스의 생성자나 소멸자가 아닙니다.

• SQL Server 프로시저에 있는 해당 매개 변수의 데이터 형식과 호환되는 매개 변수 유형을 사용합니다. .NET Framework 데이터 형식과 일치하는 SQL Server 데이터 형식에 대한 자세한 내용은 CLR 매개 변수 데이터 매핑를 참조하십시오.

• void나 SQLInt32, SQLInt16, System.Int32 또는 System.Int16 유형의 값을 반환합니다.

• 특정 매개 변수 선언에 OUTPUT이 지정되는 경우 해당 매개 변수를 값이 아닌 참조로 반환합니다.

저장 프로시저에 대한 정보 얻기

Transact-SQL 저장 프로시저의 정의를 표시하려면 프로시저가 존재하는 데이터베이스에서 sys.sql_modules 카탈로그 뷰를 사용합니다.

예를 들면 다음과 같습니다.

USE AdventureWorks;
GO
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';

[!참고]

ENCRYPTION 옵션으로 만든 저장 프로시저의 텍스트는 sys.sql_modules 카탈로그 뷰를 사용하여 볼 수 없습니다.

프로시저에서 참조하는 개체에 대한 보고서를 보려면 sys.sql_expression_dependencies 카탈로그 뷰를 쿼리하거나 sys.dm_sql_referenced_entities 및 sys.dm_sql_referencing_entities를 사용합니다.

CLR 저장 프로시저에 대한 정보를 표시하려면 프로시저가 존재하는 데이터베이스에서 sys.assembly_modules 카탈로그 뷰를 사용합니다.

저장 프로시저에 정의된 매개 변수에 대한 정보를 표시하려면 프로시저가 존재하는 데이터베이스에서 sys.parameters 카탈로그 뷰를 사용합니다.

지연된 이름 확인

아직 존재하지 않는 테이블을 참조하는 저장 프로시저를 만들 수 있습니다. 저장 프로시저를 만들 때는 구문 검사만 수행되며 처음 실행할 때까지는 저장 프로시저가 컴파일되지 않습니다. 컴파일 중에만 저장 프로시저에서 참조되는 모든 개체가 확인됩니다. 따라서 구문이 정확하면서 존재하지 않는 테이블을 참조하는 저장 프로시저를 만들 수는 있지만 참조되는 테이블이 없을 경우 저장 프로시저는 런타임에 오류가 발생합니다. 자세한 내용은 지연된 이름 확인 및 컴파일을 참조하십시오.

저장 프로시저 실행

사용자 정의 저장 프로시저를 일괄 처리로 실행하거나 사용자 정의 저장 프로시저 또는 함수 같은 모듈 내에서 실행할 때는 저장 프로시저 이름을 스키마 이름으로 한정하는 것이 좋습니다.

매개 변수 값을 받도록 저장 프로시저를 작성하면 매개 변수 값을 지정할 수 있습니다. 값은 상수 또는 변수로 지정해야 합니다. 함수 이름은 매개 변수 값으로 지정할 수 없습니다. 변수는 사용자 정의 변수 또는 시스템 변수(예: @@SPID)가 될 수 있습니다.

자세한 내용은 저장 프로시저 실행(데이터베이스 엔진)을 참조하십시오.

프로시저를 처음 실행하면 데이터 검색을 위한 최적 액세스 계획을 결정하기 위해 프로시저가 컴파일됩니다. 이후에 저장 프로시저를 실행할 때는 이미 생성된 계획이 데이터베이스 엔진의 계획 캐시에 남아 있을 경우 해당 계획을 다시 사용할 수 있습니다. 자세한 내용은 실행 계획 캐싱 및 다시 사용을 참조하십시오.

cursor 데이터 형식을 사용하는 매개 변수

Transact-SQL 저장 프로시저는 OUTPUT 매개 변수에만 cursor 데이터 형식을 사용할 수 있습니다. 매개 변수에 cursor 데이터 형식을 지정할 경우 VARYING과 OUTPUT 매개 변수가 모두 필요합니다. 매개 변수에 VARYING 키워드를 지정하면 데이터 형식은 cursor이어야 하며 OUTPUT 키워드를 지정해야 합니다. 자세한 내용은 OUTPUT 매개 변수에 cursor 데이터 형식 사용을 참조하십시오.

임시 저장 프로시저

데이터베이스 엔진에서는 로컬 및 전역 임시 프로시저라는 두 종류의 임시 프로시저를 지원합니다. 로컬 임시 프로시저는 해당 프로시저를 만든 연결에만 표시되고 전역 임시 프로시저는 모든 연결에서 사용할 수 있습니다. 또한 로컬 임시 프로시저는 현재 세션이 종료될 때 자동으로 삭제되지만 전역 임시 프로시저는 해당 프로시저를 사용하는 마지막 세션이 종료될 때 삭제됩니다. 자세한 내용은 저장 프로시저 만들기(데이터베이스 엔진)를 참조하십시오.

저장 프로시저 자동 실행

SQL Server 시작 시 하나 이상의 저장 프로시저를 자동으로 실행할 수 있습니다. 이러한 저장 프로시저는 시스템 관리자가 master 데이터베이스에 만들어야 하며 sysadmin 고정 서버 역할에서 백그라운드 프로세스로 실행되어야 합니다. 프로시저에 입력 또는 출력 매개 변수는 사용할 수 없습니다. 자세한 내용은 저장 프로시저 자동 실행을 참조하십시오.

저장 프로시저 중첩

저장 프로시저는 중첩될 수 있습니다. 즉, 한 저장 프로시저에서 다른 저장 프로시저를 호출할 수 있습니다. 호출된 프로시저의 실행이 시작되면 중첩 수준이 늘어나고 호출된 프로시저의 실행이 끝나면 줄어듭니다. 저장 프로시저는 최대 32수준까지 중첩될 수 있습니다. 자세한 내용은 저장 프로시저 중첩을 참조하십시오.

컴파일된 저장 프로시저의 크기를 예측하려면 다음과 같은 성능 모니터 카운터를 사용합니다.

성능 모니터 개체 이름	성능 모니터 카운터 이름
SQLServer: Plan Cache 개체	Cache Hit Ratio
	Cache Pages
	Cache Object Counts*

* 이 카운터는 임시 sql, 준비된 sql, 프로시저, 트리거 등 여러 종류의 캐시 개체에서 사용할 수 있습니다.

자세한 내용은 SQL Server, Plan Cache 개체를 참조하십시오.

<sql_statement> 제한 사항

SET SHOWPLAN_TEXT와 SET SHOWPLAN_ALL을 제외한 모든 SET 문을 저장 프로시저 내에 지정할 수 있습니다. 이러한 문은 일괄 처리에서 유일한 문이어야 합니다. 선택된 SET 옵션은 저장 프로시저가 실행되는 동안만 유지되고 다시 원래 설정으로 돌아갑니다.

저장 프로시저 내에서는 CREATE, ALTER 또는 DROP 문 같은 모든 DDL(데이터 정의 언어) 문, DBCC 문, EXECUTE 및 동적 SQL 문에서 사용된 개체 이름을 개체 스키마 이름으로 한정해야 저장 프로시저 소유자가 아닌 사용자가 저장 프로시저를 사용할 수 있습니다. 자세한 내용은 저장 프로시저 디자인(데이터베이스 엔진)을 참조하십시오.

사용 권한

데이터베이스의 CREATE PROCEDURE 권한과 프로시저를 만들 스키마에 대한 ALTER 권한이 필요합니다.

CLR 저장 프로시저의 경우 <method_specifier>에서 참조되는 어셈블리에 대한 소유권 또는 해당 어셈블리에 대한 REFERENCES 권한이 필요합니다.

예

1. 단순 프로시저 사용

다음 저장 프로시저에서는 뷰에서 모든 직원(성과 이름 제공됨), 직함 및 부서 이름을 반환합니다. 이 저장 프로시저는 매개 변수를 사용하지 않습니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

uspGetEmployees 저장 프로시저는 다음과 같은 방법으로 실행할 수 있습니다.

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

2. 단순 프로시저에 매개 변수 사용

다음 저장 프로시저에서는 뷰에서 지정된 한 명의 직원(성과 이름 제공됨), 직함 및 부서 이름을 반환합니다. 이 저장 프로시저는 전달된 매개 변수와 정확히 일치하는 항목만 받습니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 

    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

uspGetEmployees 저장 프로시저는 다음과 같은 방법으로 실행할 수 있습니다.

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

3. 단순 프로시저에 와일드카드 매개 변수 사용

다음 저장 프로시저에서는 뷰에서 지정된 직원(성과 이름 제공됨), 직함 및 부서 이름을 반환합니다. 이 저장 프로시저는 전달된 매개 변수에 패턴 일치를 사용하고 매개 변수가 없으면 미리 설정된 기본값(D로 시작되는 성)을 사용합니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

uspGetEmployees2 저장 프로시저는 여러 가지 조합으로 실행할 수 있습니다. 몇 가지 조합을 예로 들면 다음과 같습니다.

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

4. 둘 이상의 결과 집합 반환

다음 저장 프로시저는 두 개의 결과 집합을 반환합니다.

USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults 
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO

5. OUTPUT 매개 변수 사용

다음 예에서는 uspGetList 저장 프로시저를 만듭니다. 이 프로시저에서는 가격이 지정한 금액을 초과하지 않는 제품 목록을 반환합니다. 다음 예에서는 여러 SELECT 문과 여러 OUTPUT 매개 변수의 사용 방법을 보여 줍니다. OUTPUT 매개 변수는 프로시저를 실행하는 동안 외부 프로시저, 일괄 처리 또는 둘 이상의 Transact-SQL 문이 값 집합을 액세스하도록 허용합니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

uspGetList를 실행하여 가격이 $700 미만인 Adventure Works 제품(자전거) 목록을 반환합니다. OUTPUT 매개 변수인 @Cost와 @ComparePrices는 메시지 창의 메시지를 반환하기 위해 흐름 제어 언어와 함께 사용됩니다.

[!참고]

프로시저가 만들어질 때뿐 아니라 변수가 사용될 때도 OUTPUT 변수를 정의해야 합니다. 매개 변수 이름과 변수 이름은 일치하지 않아도 되지만 @listprice= variable을 사용한 경우가 아니라면 데이터 형식과 매개 변수 위치가 일치해야 합니다.

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

다음은 결과 집합의 일부입니다.

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

6. WITH RECOMPILE 옵션 사용

WITH RECOMPILE 절은 프로시저에 제공되는 매개 변수가 일반적이지 않으며 새 실행 계획이 메모리에 캐시되거나 저장되지 않을 때 유용합니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v 
    JOIN Purchasing.ProductVendor AS pv 
      ON v.VendorID = pv.VendorID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

7. WITH ENCRYPTION 옵션 사용

다음 예에서는 HumanResources.uspEncryptThis 저장 프로시저를 만듭니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

WITH ENCRYPTION 옵션은 다음 예에 표시된 것처럼 저장 프로시저의 정의가 반환되지 않도록 합니다.

sp_helptext를 실행합니다.

EXEC sp_helptext 'HumanResources.uspEncryptThis';

결과 집합은 다음과 같습니다.

The text for object 'HumanResources.uspEncryptThis' is encrypted.

sys.sql_modules 카탈로그 뷰를 직접 쿼리합니다.

USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

결과 집합은 다음과 같습니다.

definition
----------------------
NULL

(1 row(s) affected)

8. 지연된 이름 확인 사용

다음 예에서는 uspProc1 프로시저를 만듭니다. 이 프로시저에서는 지연된 이름 확인을 사용합니다. 참조되는 테이블이 컴파일 시 존재하지 않더라도 해당 저장 프로시저가 생성됩니다. 그러나 프로시저가 실행될 때는 테이블이 있어야 합니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SET NOCOUNT ON;
    SELECT column1, column2 FROM table_does_not_exist
GO

저장 프로시저가 만들어졌는지 확인하려면 다음 쿼리를 실행합니다.

USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');

결과 집합은 다음과 같습니다.

definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)

9. EXECUTE AS 절 사용

다음 예에서는 EXECUTE AS 절을 사용하여 저장 프로시저가 실행될 수 있는 보안 컨텍스트를 지정하는 방법을 보여 줍니다. 이 예에서 CALLER 옵션은 프로시저를 호출하는 사용자 컨텍스트에서 프로시저가 실행될 수 있도록 지정합니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name', 
      v.CreditRating AS 'Credit Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.VendorID = pv.VendorID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

10. CLR 저장 프로시저 만들기

다음 예에서는 HandlingLOBUsingCLR 어셈블리에 있는 LargeObjectBinary 클래스의 GetPhotoFromDB 메서드를 참조하는 GetPhotoFromDB 저장 프로시저를 만듭니다. 저장 프로시저를 만들기 전에 HandlingLOBUsingCLR 어셈블리가 로컬 데이터베이스에 등록됩니다.

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
    @ProductPhotoID int,
    @CurrentDirectory nvarchar(1024),
    @FileName nvarchar(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO

11. OUTPUT 커서 매개 변수 사용

OUTPUT 커서 매개 변수는 저장 프로시저에서 로컬로 사용되는 커서를 호출한 일괄 처리, 저장 프로시저 또는 트리거에 다시 전달하는 데 사용됩니다.

먼저 커서를 선언하여 Currency 테이블에서 여는 프로시저를 만듭니다.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor 
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

다음으로 로컬 커서 변수를 선언하는 일괄 처리를 실행하고, 지역 변수에 커서를 할당하는 프로시저를 실행한 다음, 커서에서 행을 인출합니다.

USE AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

참고 항목

태스크

방법: 저장 프로시저 만들기(SQL Server Management Studio)

참조

ALTER PROCEDURE(Transact-SQL)

흐름 제어 언어(Transact-SQL)

데이터 형식(Transact-SQL)

DECLARE @local\_variable(Transact-SQL)

DROP PROCEDURE(Transact-SQL)

EXECUTE(Transact-SQL)

EXECUTE AS(Transact-SQL)

EVENTDATA(Transact-SQL)

함수(Transact-SQL)

sp_procoption(Transact-SQL)

sp_recompile(Transact-SQL)

sys.sql_modules(Transact-SQL)

sys.parameters(Transact-SQL)

sys.procedures(Transact-SQL)

sys.sql_expression_dependencies(Transact-SQL)

sys.assembly_modules(Transact-SQL)

sys.numbered_procedures(Transact-SQL)

sys.numbered_procedure_parameters(Transact-SQL)

OBJECT_DEFINITION(Transact-SQL)

sys.dm_sql_referenced_entities(Transact-SQL)

sys.dm_sql_referencing_entities(Transact-SQL)

개념

일괄 처리

커서(데이터베이스 엔진)

변수 및 매개 변수 사용(데이터베이스 엔진)

테이블 반환 매개 변수(데이터베이스 엔진)

관련 자료

저장 프로시저(데이터베이스 엔진)