CREATE PROCEDURE(Transact-SQL)CREATE PROCEDURE (Transact-SQL)

이 항목은 다음에 적용됩니다. 예SQL Server(2008부터)예Azure SQL Database예Azure SQL Data Warehouse 예병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

만듭니다는 Transact-SQLTransact-SQL 또는 공용 언어 런타임 (CLR) 저장 프로시저에서 SQL ServerSQL Server, Azure SQL 데이터베이스Azure SQL Database, Azure SQL 데이터 웨어하우스 및 병렬 데이터 웨어하우스 합니다.Creates a Transact-SQLTransact-SQL or common language runtime (CLR) stored procedure in SQL ServerSQL Server, Azure SQL 데이터베이스Azure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse. 저장 프로시저는 다음과 같은 점에서 다른 프로그래밍 언어의 프로시저와 유사합니다.Stored procedures are similar to procedures in other programming languages in that they can:

  • 입력 매개 변수를 받아 여러 값을 출력 매개 변수의 형태로 호출하는 프로시저 또는 일괄 처리에 반환합니다.Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

  • 다른 프로시저 호출을 비롯하여 데이터베이스에서 작업을 수행하는 프로그래밍 문이 포함되어 있습니다.Contain programming statements that perform operations in the database, including calling other procedures.

  • 호출하는 프로시저 또는 일괄 처리에 상태 값을 반환하여 성공 또는 실패 및 실패 원인을 나타냅니다.Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

    이 문을 사용 하 여 현재 데이터베이스 이름이 나에 임시 프로시저에 영구 프로시저를 만들는 tempdb 데이터베이스입니다.Use this statement to create a permanent procedure in the current database or a temporary procedure in the tempdb database.

참고

SQL Server의 통합의.NET Framework CLR은이 항목에서 설명 합니다.The integration of .NET Framework CLR into SQL Server is discussed in this topic. CLR 통합 Azure에 적용 되지 않습니다 SQL 데이터베이스SQL Database합니다.CLR integration does not apply to Azure SQL 데이터베이스SQL Database.

이동할 간단한 예 구문의 세부 정보 생략 하 고는 빠른 예제는 기본 저장 프로시저입니다.Jump to Simple Examples to skip the details of the syntax and get to a quick example of a basic stored procedure.

항목 링크 아이콘 Transact-SQL 구문 규칙Topic link icon Transact-SQL Syntax Conventions

구문Syntax

-- Transact-SQL Syntax for Stored Procedures in SQL Server and Azure SQL Database  

CREATE [ OR ALTER ] { 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 { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }  
[;]  

<procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]  
    [ EXECUTE AS Clause ]  
-- Transact-SQL Syntax for CLR Stored Procedures  

CREATE [ OR ALTER ] { PROC | PROCEDURE } 
    [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ type_schema_name. ] data_type }   
        [ = default ] [ OUT | OUTPUT ] [READONLY]  
    ] [ ,...n ]   
[ WITH EXECUTE AS Clause ]  
AS { EXTERNAL NAME assembly_name.class_name.method_name }  
[;]  
-- Transact-SQL Syntax for Natively Compiled Stored Procedures  

CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name  
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ] 
        [ OUT | OUTPUT ] [READONLY] 
    ] [ ,... n ]  
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]  
AS  
{  
  BEGIN ATOMIC WITH (set_option [ ,... n ] )  
sql_statement [;] [ ... n ]  
 [ END ]  
}  
 [;]  

<set_option> ::=  
    LANGUAGE =  [ N ] 'language'  
  | TRANSACTION ISOLATION LEVEL =  { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }  
  | [ DATEFIRST = number ]  
  | [ DATEFORMAT = format ]  
  | [ DELAYED_DURABILITY = { OFF | ON } ]  
-- Transact-SQL Syntax for Stored Procedures in Azure SQL Data Warehouse
-- and Parallel Data Warehouse  

-- Create a stored procedure   
CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name  
    [ { @parameterdata_type } [ OUT | OUTPUT ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [;][ ,...n ] [ END ] }  
[;]  

인수Arguments

또는 변경OR ALTER
적용 대상: Azure SQL 데이터베이스SQL Database, SQL ServerSQL Server (부터는 SQL Server 2016SQL Server 2016 SP1).Applies to: Azure SQL 데이터베이스SQL Database, SQL ServerSQL Server (starting with SQL Server 2016SQL Server 2016 SP1).

이미 있는 경우 프로시저를 변경 합니다.Alters the procedure if it already exists.

schema_nameschema_name
프로시저가 속한 스키마의 이름입니다.The name of the schema to which the procedure belongs. 프로시저는 스키마 바운드 개체입니다.Procedures are schema-bound. 프로시저를 만들 때 스키마 이름을 지정하지 않으면 프로시저를 만드는 사용자의 기본 스키마가 자동으로 할당됩니다.If a schema name is not specified when the procedure is created, the default schema of the user who is creating the procedure is automatically assigned.

procedure_nameprocedure_name
프로시저의 이름입니다.The name of the procedure. 프로시저 이름에 대 한 규칙을 준수 해야 식별자 하며 스키마 내에서 고유 해야 합니다.Procedure names must comply with the rules for identifiers and must be unique within the schema.

사용 하지는 sp_ 프로시저 이름을 지정할 때 접두사입니다.Avoid the use of the sp_ prefix when naming procedures. 이 접두사는 SQL ServerSQL Server에서 시스템 프로시저를 지정하는 데 사용됩니다.This prefix is used by SQL ServerSQL Server to designate system procedures. 이 접두사를 사용하면 같은 이름의 시스템 프로시저가 있을 경우 응용 프로그램 코드가 손상될 수 있습니다.Using the prefix can cause application code to break if there is a system procedure with the same name.

하기 전에 하나의 숫자 기호 (#)를 사용 하 여 로컬 또는 전역 임시 프로시저를 만들 수 있습니다 procedure_name (#procedure_name) 로컬 임시 프로시저 및 전역 임시에 대 한 두 개의 숫자 기호에 대 한 프로시저 (# # procedure_name).Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). 로컬 임시 프로시저는 해당 프로시저를 만든 연결에만 표시되고 해당 연결을 닫을 때 삭제됩니다.A local temporary procedure is visible only to the connection that created it and is dropped when that connection is closed. 전역 임시 프로시저는 모든 연결에서 사용할 수 있고 해당 프로시저를 사용하는 마지막 세션이 종료될 때 삭제됩니다.A global temporary procedure is available to all connections and is dropped at the end of the last session using the procedure. CLR 프로시저에는 임시 이름을 지정할 수 없습니다.Temporary names cannot be specified for CLR procedures.

프로시저 또는 전역 임시 프로시저의 전체 이름은 ##을 포함하여 128자를 초과할 수 없습니다.The complete name for a procedure or a global temporary procedure, including ##, cannot exceed 128 characters. 로컬 임시 프로시저의 전체 이름은 #을 포함하여 116자를 초과할 수 없습니다.The complete name for a local temporary procedure, including #, cannot exceed 116 characters.

; 번호; number
적용 대상: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017Azure SQL 데이터베이스Azure SQL Database합니다.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL 데이터베이스Azure SQL Database.

같은 이름의 프로시저를 그룹화하는 데 사용하는 정수입니다(선택 사항).An optional integer that is used to group procedures of the same name. 이렇게 그룹화된 프로시저는 DROP PROCEDURE 문 하나를 사용하여 한꺼번에 삭제할 수 있습니다.These grouped procedures can be dropped together by using one DROP PROCEDURE statement.

참고

Microsoft SQL Server의 이후 버전에서는 이 기능이 제거됩니다.This feature will be removed in a future version of Microsoft SQL Server. 새 개발 작업에서는 이 기능을 사용하지 않도록 하고, 현재 이 기능을 사용하는 응용 프로그램은 수정하세요.Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

번호가 매겨진된 프로시저를 사용할 수 없습니다는 xml 또는 CLR 사용자 정의 형식 및 계획 지침에 사용할 수 없습니다.Numbered procedures cannot use the xml or CLR user-defined types and cannot be used in a plan guide.

@매개 변수@ parameter
프로시저에 선언된 매개 변수입니다.A parameter declared in the procedure. 매개 변수 이름을 사용 하 여 지정 된 at 기호 (@) 첫 번째 문자로 합니다.Specify a parameter name by using the at sign (@) as the first character. 매개 변수 이름에 대 한 규칙을 준수 해야 식별자합니다.The parameter name must comply with the rules for identifiers. 매개 변수는 프로시저에서 로컬로 사용되므로 다른 프로시저에서 동일한 매개 변수 이름을 사용할 수 있습니다.Parameters are local to the procedure; the same parameter names can be used in other procedures.

하나 이상의 매개 변수를 선언할 수 있으며 최대 2,100개까지 선언할 수 있습니다.One or more parameters can be declared; the maximum is 2,100. 선언된 각 매개 변수의 값은 기본값이 정의된 경우나 다른 매개 변수와 값이 같도록 설정된 경우를 제외하면 프로시저가 호출될 때 사용자가 지정해야 합니다.The value of each declared parameter must be supplied by the user when the procedure is called unless a default value for the parameter is defined or the value is set to equal another parameter. 프로시저는 포함 된 경우 테이블 반환 매개 변수, 및 매개 변수는 호출에서 누락, 빈 테이블이 전달 됩니다.If a procedure contains table-valued parameters, and the parameter is missing in the call, an empty table is passed in. 매개 변수는 상수 식 대신 사용할 수 있지만 테이블 이름, 열 이름 또는 다른 데이터베이스 개체의 이름 대신 사용할 수는 없습니다.Parameters can take the place only of constant expressions; they cannot be used instead of table names, column names, or the names of other database objects. 자세한 내용은 EXECUTE(Transact-SQL)을 참조하세요.For more information, see EXECUTE (Transact-SQL).

FOR REPLICATION을 지정하면 매개 변수를 선언할 수 없습니다.Parameters cannot be declared if FOR REPLICATION is specified.

[ type_schema_name합니다.[ type_schema_name. ] data_type] data_type
매개 변수의 데이터 형식 및 해당 데이터 형식이 속하는 스키마입니다.The data type of the parameter and the schema to which the data type belongs.

에 대 한 지침이 Transact-SQLTransact-SQL 프로시저:Guidelines for Transact-SQLTransact-SQL procedures:

  • 모든 Transact-SQLTransact-SQL 데이터 형식은 매개 변수로 사용할 수 있습니다.All Transact-SQLTransact-SQL data types can be used as parameters.

  • 사용자 정의 테이블 형식을 사용하여 테이블 반환 매개 변수를 만들 수 있습니다.You can use the user-defined table type to create table-valued parameters. 테이블 반환 매개 변수는 INPUT 매개 변수로만 지정할 수 있으며 READONLY 키워드와 함께 사용해야 합니다.Table-valued parameters can only be INPUT parameters and must be accompanied by the READONLY keyword. 자세한 내용은 참조 테이블 반환 매개 변수 ( 데이터베이스 엔진 )For more information, see Use Table-Valued Parameters (Database Engine)

  • 커서 데이터 형식 출력 매개 변수로 지정할 수 있습니다 및 VARYING 키워드와 함께 제공 합니다.cursor data types can only be OUTPUT parameters and must be accompanied by the VARYING keyword.

CLR 프로시저에 대 한 지침이:Guidelines for CLR procedures:

  • 관리 코드에 해당 형식이 있는 모든 네이티브 SQL ServerSQL Server 데이터 형식을 매개 변수로 사용할 수 있습니다.All of the native SQL ServerSQL Server data types that have an equivalent in managed code can be used as parameters. CLR 형식 간의 관계에 대 한 자세한 내용은 및 SQL ServerSQL Server 시스템 데이터 형식을 참조 CLR 매개 변수 데이터 매핑합니다.For more information about the correspondence between CLR types and SQL ServerSQL Server system data types, see Mapping CLR Parameter Data. 에 대 한 자세한 내용은 SQL ServerSQL Server 시스템 데이터 형식과 해당 구문 참조 데이터 형식 ( Transact SQL ) .For more information about SQL ServerSQL Server system data types and their syntax, see Data Types (Transact-SQL).

  • 테이블 반환 또는 커서 데이터 형식은 매개 변수로 사용할 수 없습니다.Table-valued or cursor data types cannot be used as parameters.

  • 매개 변수의 데이터 형식이 CLR 사용자 정의 형식인 경우 해당 형식에 대해 EXECUTE 권한이 있어야 합니다.If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.

VARYINGVARYING
결과 집합이 출력 매개 변수로 사용되도록 지정합니다.Specifies the result set supported as an output parameter. 이 매개 변수는 프로시저에 의해 동적으로 생성될 수 있으며 해당 내용은 여러 가지가 될 수 있습니다.This parameter is dynamically constructed by the procedure and its contents may vary. 에 적용 됩니다 커서 매개 변수입니다.Applies only to cursor parameters. CLR 프로시저에는 이 옵션이 유효하지 않습니다.This option is not valid for CLR procedures.

기본값default
매개 변수의 기본값을 나타냅니다.A default value for a parameter. 매개 변수에 대해 기본값이 정의 경우 해당 매개 변수에 대해 값을 지정 하지 않고 프로시저를 실행할 수 있습니다.If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. 기본값은 상수이거나 NULL입니다.The default value must be a constant or it can be NULL. 상수 값을 와일드카드 형태로 지정할 수 있으므로 프로시저에 매개 변수를 전달할 때 LIKE 키워드를 사용할 수 있습니다.The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.

기본 값에 기록 되는 sys.parameters.default CLR 프로시저에 대 한 열입니다.Default values are recorded in the sys.parameters.default column only for CLR procedures. 해당 열에 대해 NULL 이다 Transact-SQLTransact-SQL 프로시저 매개 변수입니다.That column is NULL for Transact-SQLTransact-SQL procedure parameters.

OUT | OUTPUTOUT | OUTPUT
매개 변수가 출력 매개 변수임을 나타냅니다.Indicates that the parameter is an output parameter. OUTPUT 매개 변수를 사용하여 프로시저의 호출자에 값을 반환할 수 있습니다.Use OUTPUT parameters to return values to the caller of the procedure. 텍스트, ntext, 및 이미지 프로시저가 CLR 프로시저가 아닐 경우 출력 매개 변수로 매개 변수를 사용할 수 없습니다.text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. 프로시저가 CLR 프로시저가 아닐 경우 출력 매개 변수는 커서 자리 표시자일 수 있습니다.An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure. 테이블 반환 데이터 형식은 프로시저의 OUTPUT 매개 변수로 지정할 수 없습니다.A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

READONLYREADONLY
프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음을 나타냅니다.Indicates that the parameter cannot be updated or modified within the body of the procedure. 매개 변수 형식이 테이블 반환 형식인 경우 READONLY를 지정해야 합니다.If the parameter type is a table-value type, READONLY must be specified.

RECOMPILERECOMPILE
나타냅니다는 데이터베이스 엔진Database Engine 실행 될 때마다 컴파일한다 수는이 절차에 대 한 쿼리 계획을 캐시 하지 않습니다.Indicates that the 데이터베이스 엔진Database Engine does not cache a query plan for this procedure, forcing it to be compiled each time it is executed. 다시 컴파일을 적용 하는 목적에 대 한 자세한 내용은 참조 저장 프로시저를 다시 컴파일하려면합니다.For more information regarding the reasons for forcing a recompile, see Recompile a Stored Procedure. CLR 프로시저에 대해 FOR REPLICATION을 지정한 경우에는 이 옵션을 사용할 수 없습니다.This option cannot be used when FOR REPLICATION is specified or for CLR procedures.

지시 하는 데이터베이스 엔진Database Engine 프로시저 안에 있는 개별 쿼리에 대 한 쿼리 계획을 삭제 하려면 쿼리 정의에서 RECOMPILE 쿼리 힌트를 사용 합니다.To instruct the 데이터베이스 엔진Database Engine to discard query plans for individual queries inside a procedure, use the RECOMPILE query hint in the definition of the query. 자세한 내용은 쿼리 힌트(Transact-SQL)를 참조하세요.For more information, see Query Hints (Transact-SQL).

ENCRYPTIONENCRYPTION
적용 대상: SQL Server ( SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017), Azure SQL 데이터베이스Azure SQL Database합니다.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Azure SQL 데이터베이스Azure SQL Database.

나타냅니다 SQL ServerSQL Server CREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운된 형식으로 변환 합니다.Indicates that SQL ServerSQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. 난독 처리의 출력의 카탈로그 뷰 중 하나에 직접 표시 되지 않습니다. SQL ServerSQL Server합니다.The output of the obfuscation is not directly visible in any of the catalog views in SQL ServerSQL Server. 시스템 테이블 또는 데이터베이스 파일에 대한 액세스 권한이 없는 사용자는 변조된 텍스트를 검색할 수 없습니다.Users who have no access to system tables or database files cannot retrieve the obfuscated text. 그러나 텍스트를 통해 시스템 테이블에 액세스 하거나 수 있는 권한이 있는 사용자를 사용할 수는 DAC 포트 또는 데이터베이스 파일에 직접 액세스 합니다.However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files. 또한 디버거를 서버 프로세스에 연결할 수 있는 사용자는 런타임에 메모리에서 해독된 프로시저를 검색할 수 있습니다.Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. 시스템 메타 데이터에 액세스 하는 방법에 대 한 자세한 내용은 참조 메타 데이터 표시 유형 구성은합니다.For more information about accessing system metadata, see Metadata Visibility Configuration.

CLR 프로시저에는 이 옵션이 유효하지 않습니다.This option is not valid for CLR procedures.

이 옵션을 사용 하 여 만든 프로시저의 일부로 게시할 수 없습니다 SQL ServerSQL Server 복제 합니다.Procedures created with this option cannot be published as part of SQL ServerSQL Server replication.

EXECUTE AS EXECUTE AS clause
프로시저를 실행할 보안 컨텍스트를 지정합니다.Specifies the security context under which to execute the procedure.

시작 하는 고유 하 게 컴파일된 저장된 프로시저에 대 한 SQL Server 2016SQL Server 2016Azure SQL 데이터베이스Azure SQL Database는 EXECUTE AS에 제한이 없습니다 절.For natively compiled stored procedures, starting SQL Server 2016SQL Server 2016 and in Azure SQL 데이터베이스Azure SQL Database, there are no limitations on the EXECUTE AS clause. SQL Server 2014SQL Server 2014 SELF, OWNER 및 'r _' 절은 고유 하 게 컴파일된 저장된 프로시저와 함께 지원 됩니다.In SQL Server 2014SQL Server 2014 the SELF, OWNER, and ‘user_name’ clauses are supported with natively compiled stored procedures.

자세한 내용은 EXECUTE AS 절(Transact-SQL)을 참조하세요.For more information, see EXECUTE AS Clause (Transact-SQL).

FOR REPLICATIONFOR REPLICATION
적용 대상: SQL Server ( SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017), Azure SQL 데이터베이스Azure SQL Database합니다.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Azure SQL 데이터베이스Azure SQL Database.

프로시저가 복제용으로 생성되도록 지정합니다.Specifies that the procedure is created for replication. 따라서 구독자에서는 프로시저를 실행할 수 없습니다.Consequently, it cannot be executed on the Subscriber. FOR REPLICATION 옵션을 사용하여 만든 프로시저는 프로시저 필터로 사용되며 복제하는 동안에만 실행됩니다.A procedure created with the FOR REPLICATION option is used as a procedure filter and is executed only during replication. FOR REPLICATION을 지정하면 매개 변수를 선언할 수 없습니다.Parameters cannot be declared if FOR REPLICATION is specified. CLR 프로시저에는 FOR REPLICATION을 지정할 수 없습니다.FOR REPLICATION cannot be specified for CLR procedures. FOR REPLICATION으로 만든 프로시저의 경우 RECOMPILE 옵션이 무시됩니다.The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

A FOR REPLICATION 프로시저에는 개체 유형 RFsys.objectssys.procedures합니다.A FOR REPLICATION procedure has an object type RF in sys.objects and sys.procedures.

{[BEGIN] sql_statement [;] [ ... n ] [END]을 (를){ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
프로시저 본문을 구성하는 하나 이상의 Transact-SQLTransact-SQL 문입니다.One or more Transact-SQLTransact-SQL statements comprising the body of the procedure. 선택적 키워드인 BEGIN과 END를 사용하여 문을 묶을 수 있습니다.You can use the optional BEGIN and END keywords to enclose the statements. 자세한 내용은 다음에 나오는 최선의 구현 방법, 일반적인 주의 사항 및 제한 사항 섹션을 참조하세요.For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

외부 이름 assembly_name. class_name. method_nameEXTERNAL NAME assembly_name.class_name.method_name
적용 대상: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017, SQL 데이터베이스SQL Database합니다.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL 데이터베이스SQL Database.

CLR 저장 프로시저가 참조할 .NET Framework.NET Framework 어셈블리의 메서드를 지정합니다.Specifies the method of a .NET Framework.NET Framework assembly for a CLR procedure to reference. class_name 은 유효한 SQL ServerSQL Server 식별자 하며 어셈블리에서 클래스로 존재 해야 합니다.class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly. 있는 경우 클래스에 마침표를 사용 하는 정규화 된 네임 스페이스 이름은 (.) 네임 스페이스 부분을 구분 하 클래스 이름은 대괄호를 사용 하 여 구분 합니다 (*) 나 따옴표 (*"").If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([]) or quotation marks (""). 지정한 메서드는 해당 클래스의 정적 메서드여야 합니다.The specified method must be a static method of the class.

기본적으로 SQL ServerSQL Server에서는 CLR 코드를 실행할 수 없습니다.By default, SQL ServerSQL Server cannot execute CLR code. 만들기, 수정 및 공용 언어 런타임 모듈; 참조 하는 데이터베이스 개체를 삭제 합니다. 그러나 이러한 참조를 실행할 수 없습니다 SQL ServerSQL Server 설정할 때까지 clr enabled 옵션합니다.You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. 옵션을 사용 하려면 sp_configure합니다.To enable the option, use sp_configure.

참고

CLR 프로시저는 포함된 데이터베이스에서 지원되지 않습니다.CLR procedures are not supported in a contained database.

ATOMIC WITHATOMIC WITH
적용 대상: SQL Server 2014SQL Server 2014 통해 SQL Server 2017SQL Server 2017Azure SQL 데이터베이스Azure SQL Database합니다.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL 데이터베이스Azure SQL Database.

원자성 저장 프로시저 실행을 나타냅니다.Indicates atomic stored procedure execution. 변경 내용이 커밋되거나 모든 변경 내용이 예외를 발생하고 롤백됩니다.Changes are either committed or all of the changes rolled back by throwing an exception. ATOMIC WITH 블록은 고유하게 컴파일된 저장 프로시저에 필요합니다.The ATOMIC WITH block is required for natively compiled stored procedures.

명시적으로 RETRUN 문을 사용하거나 암시적으로 실행을 완료해서 프로시저가 RETURN되면 프로시저로 수행된 작업이 커밋됩니다.If the procedure RETURNs (explicitly through the RETURN statement, or implicitly by completing execution), the work performed by the procedure is committed. 프로시저가 THROW하면 프로시저로 수행된 작업이 롤백됩니다.If the procedure THROWs, the work performed by the procedure is rolled back.

XACT_ABORT는 기본적으로 원자성 블록 내에서 ON이며 변경할 수 없습니다.XACT_ABORT is ON by default inside an atomic block and cannot be changed. XACT_ABORT는 SQL ServerSQL Server 문이 런타임 오류를 일으킬 때 Transact-SQLTransact-SQL가 현재 트랜잭션을 자동으로 롤백할지 여부를 지정합니다.XACT_ABORT specifies whether SQL ServerSQL Server automatically rolls back the current transaction when a Transact-SQLTransact-SQL statement raises a run-time error.

다음 SET 옵션은 ATOMIC 블록에서 항상 ON이며 이 옵션은 변경할 수 없습니다.The following SET options are always ON in the ATOMIC block; the options cannot be changed.

  • CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER, ARITHABORTQUOTED_IDENTIFIER, ARITHABORT
  • NOCOUNTNOCOUNT
  • ANSI_NULLSANSI_NULLS
  • ANSI_WARNINGSANSI_WARNINGS

SET 옵션은 ATOMIC 블록 내에서 변경할 수 없습니다.SET options cannot be changed inside ATOMIC blocks. 사용자 세션에서 SET 옵션은 고유하게 컴파일된 저장 프로시저의 범위에 사용되지 않습니다.The SET options in the user session are not used in the scope of natively compiled stored procedures. 이러한 옵션은 컴파일 시에 고정됩니다.These options are fixed at compile time.

BEGIN, ROLLBACK 및 COMMIT 작업은 원자성 블록 내에서 사용할 수 없습니다.BEGIN, ROLLBACK, and COMMIT operations cannot be used inside an atomic block.

프로시저 외부 범위에 고유하게 컴파일된 저장 프로시저당 한 개의 ATOMIC 블록이 있습니다.There is one ATOMIC block per natively compiled stored procedure, at the outer scope of the procedure. 블록은 중첩될 수 없습니다.The blocks cannot be nested. Atomic 블록에 대 한 자세한 내용은 참조 Natively Compiled Stored Procedures합니다.For more information about atomic blocks, see Natively Compiled Stored Procedures.

NULL | NOT NULLNULL | NOT NULL
매개 변수에 null 값이 허용되는지 여부를 결정합니다.Determines whether null values are allowed in a parameter. NULL이 기본값입니다.NULL is the default.

NATIVE_COMPILATIONNATIVE_COMPILATION
적용 대상: SQL Server 2014SQL Server 2014 통해 SQL Server 2017SQL Server 2017Azure SQL 데이터베이스Azure SQL Database합니다.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL 데이터베이스Azure SQL Database.

프로시저가 고유하게 컴파일되었음을 나타냅니다.Indicates that the procedure is natively compiled. NATIVE_COMPILATION, SCHEMABINDING 및 EXECUTE AS는 임의 순서로 지정할 수 있습니다.NATIVE_COMPILATION, SCHEMABINDING, and EXECUTE AS can be specified in any order. 자세한 내용은 참조 Natively Compiled Stored Procedures합니다.For more information, see Natively Compiled Stored Procedures.

SCHEMABINDINGSCHEMABINDING
적용 대상: SQL Server 2014SQL Server 2014 통해 SQL Server 2017SQL Server 2017Azure SQL 데이터베이스Azure SQL Database합니다.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL 데이터베이스Azure SQL Database.

프로시저에서 참조되는 테이블을 삭제 또는 변경할 수 없도록 보장합니다.Ensures that tables that are referenced by a procedure cannot be dropped or altered. SCHEMABINDING은 고유하게 컴파일된 저장 프로시저에 필요합니다.SCHEMABINDING is required in natively compiled stored procedures. (자세한 내용은 참조 Natively Compiled Stored Procedures.) SCHEMABINDING 제한은 사용자 정의 함수의 경우와 동일합니다.(For more information, see Natively Compiled Stored Procedures.) The SCHEMABINDING restrictions are the same as they are for user-defined functions. 자세한 내용은에서 SCHEMABINDING 섹션을 참조 하십시오. CREATE function( Transact SQL ) .For more information, see the SCHEMABINDING section in CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] '언어'LANGUAGE = [N] 'language'
적용 대상: SQL Server 2014SQL Server 2014 통해 SQL Server 2017SQL Server 2017Azure SQL 데이터베이스Azure SQL Database합니다.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL 데이터베이스Azure SQL Database.

에 해당 언어 ( 설정 합니다. Transact SQL ) 세션 옵션입니다.Equivalent to SET LANGUAGE (Transact-SQL) session option. LANGUAGE = [N] '언어'는 필수입니다.LANGUAGE = [N] 'language' is required.

TRANSACTION ISOLATION LEVELTRANSACTION ISOLATION LEVEL
적용 대상: SQL Server 2014SQL Server 2014 통해 SQL Server 2017SQL Server 2017Azure SQL 데이터베이스Azure SQL Database합니다.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL 데이터베이스Azure SQL Database.

고유하게 컴파일된 저장 프로시저에 필요합니다.Required for natively compiled stored procedures. 저장 프로시저의 트랜잭션 격리 수준을 지정합니다.Specifies the transaction isolation level for the stored procedure. 다음과 같은 옵션이 있습니다.The options are as follows:

이러한 옵션에 대 한 자세한 내용은 참조 SET TRANSACTION ISOLATION LEVEL ( Transact SQL ) .For more information about these options, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READREPEATABLE READ
다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 문이 읽을 수 없도록 지정합니다.Specifies that statements cannot read data that has been modified but not yet committed by other transactions. 다른 트랜잭션이 현재 트랜잭션에서 읽은 데이터를 수정 하는 경우 현재 트랜잭션이 실패 합니다.If another transaction modifies data that has been read by the current transaction, the current transaction fails.

SERIALIZABLESERIALIZABLE
다음을 지정합니다.Specifies the following:

  • 문은 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽을 수 없습니다.Statements cannot read data that has been modified but not yet committed by other transactions.
  • 다른 트랜잭션은 현재 트랜잭션에서 읽은 데이터를 수정할 경우 현재 트랜잭션이 실패 합니다.If another transactions modifies data that has been read by the current transaction, the current transaction fails.
  • 다른 트랜잭션이 현재 트랜잭션의 문에서 읽은 키 범위에 속하는 키 값을 가진 새 행을 삽입, 현재 트랜잭션이 실패 합니다.If another transaction inserts new rows with key values that would fall in the range of keys read by any statements in the current transaction, the current transaction fails.

SNAPSHOTSNAPSHOT
트랜잭션의 문이 읽은 데이터가 트랜잭션 시작 당시 되는 데이터의 트랜잭션 측면에서 일관 된 버전 임을 지정 합니다.Specifies that data read by any statement in a transaction is the transactionally consistent version of the data that existed at the start of the transaction.

DATEFIRST = 번호DATEFIRST = number
적용 대상: SQL Server 2014SQL Server 2014 통해 SQL Server 2017SQL Server 2017Azure SQL 데이터베이스Azure SQL Database합니다.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL 데이터베이스Azure SQL Database.

일주일의 시작 요일을 1부터 7까지의 숫자로 지정합니다.Specifies the first day of the week to a number from 1 through 7. DATEFIRST는 선택 사항입니다.DATEFIRST is optional. 지정하지 않으면 지정된 언어로부터 설정이 유추됩니다.If it is not specified, the setting is inferred from the specified language.

자세한 내용은 참조 SET datefirst( Transact SQL ) .For more information, see SET DATEFIRST (Transact-SQL).

DATEFORMAT = 형식DATEFORMAT = format
적용 대상: SQL Server 2014SQL Server 2014 통해 SQL Server 2017SQL Server 2017Azure SQL 데이터베이스Azure SQL Database합니다.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL 데이터베이스Azure SQL Database.

date, smalldatetime, datetime, datetime2 및 datetimeoffset 문자열 해석을 위한 월, 일 및 연도 날짜 부분의 순서를 지정합니다.Specifies the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings. DATEFORMAT은 선택 사항입니다.DATEFORMAT is optional. 지정하지 않으면 지정된 언어로부터 설정이 유추됩니다.If it is not specified, the setting is inferred from the specified language.

자세한 내용은 참조 SET DATEFORMAT ( Transact SQL ) .For more information, see SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { OFF | ON }DELAYED_DURABILITY = { OFF | ON }
적용 대상: SQL Server 2014SQL Server 2014 통해 SQL Server 2017SQL Server 2017Azure SQL 데이터베이스Azure SQL Database합니다.Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017 and Azure SQL 데이터베이스Azure SQL Database.

SQL ServerSQL Server 트랜잭션은 완전 내구성이 있는(기본값) 커밋 또는 지연된 내구성이 있는 커밋을 수행할 수 있습니다. transaction commits can be either fully durable, the default, or delayed durable.

자세한 내용은 참조 트랜잭션 내구성 제어합니다.For more information, see Control Transaction Durability.

간단한 예 Simple Examples

를 시작할 수 있도록 두 가지 빠른 예제는 다음과 같습니다.To help you get started, here are two quick examples:
SELECT DB_NAME() AS ThisDB;현재 데이터베이스의 이름을 반환합니다.SELECT DB_NAME() AS ThisDB; returns the name of the current database.
저장된 프로시저에서와 같은 해당 문을 래핑할 수 있습니다.You can wrap that statement in a stored procedure, such as:

CREATE PROC What_DB_is_this     
AS   
SELECT DB_NAME() AS ThisDB; 

문 사용 하 여 저장 프로시저를 호출 합니다.EXEC What_DB_is_this;Call the store procedure with statement: EXEC What_DB_is_this;

좀 더 복잡 한 프로시저를 보다 유연 하 게 하는 입력된 매개 변수를 제공 하는 것입니다.Slightly more complex, is to provide an input parameter to make the procedure more flexible. 예를 들어For example:

CREATE PROC What_DB_is_that @ID int   
AS    
SELECT DB_NAME(@ID) AS ThatDB;   

프로시저를 호출 하는 경우에 데이터베이스 id 번호를 제공 합니다.Provide a database id number when you call the procedure. 예를 들어 EXEC What_DB_is_that 2; 반환 tempdb합니다.For example, EXEC What_DB_is_that 2; returns tempdb.

참조 예제 더 많은 예제를 위한이 항목의 끝까지 합니다.See Examples towards the end of this topic for many more examples.

최선의 구현 방법Best Practices

여기에 제시된 최선의 구현 방법은 일부에 불과하지만 이를 적절히 참고하면 프로시저 성능을 개선하는 데 도움이 됩니다.Although this is not an exhaustive list of best practices, these suggestions may improve procedure performance.

  • 프로시저 본문에서 SET NOCOUNT ON 문을 첫 번째 문으로 사용합니다.Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. 즉, 이 문을 AS 키워드 바로 다음에 배치합니다.That is, place it just after the AS keyword. 이렇게 하면 SELECT, INSERT, UPDATE, MERGE 및 DELETE 문이 실행된 후 SQL ServerSQL Server에서 클라이언트로 보내는 메시지가 해제됩니다.This turns off messages that SQL ServerSQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. 따라서 필요 없는 네트워크 오버헤드가 사라져 데이터베이스와 응용 프로그램의 전반적인 성능이 개선됩니다.Overall performance of the database and application is improved by eliminating this unnecessary network overhead. 자세한 내용은 참조 하세요. SET NOCOUNT ( Transact SQL ) .For information, see SET NOCOUNT (Transact-SQL).

  • 프로시저에서 데이터베이스 개체를 만들거나 참조할 때 스키마 이름을 사용합니다.Use schema names when creating or referencing database objects in the procedure. 에 대 한 처리 시간이 걸리는 데이터베이스 엔진Database Engine 여러 스키마를 검색 하려면 없는 경우 개체 이름을 확인할 수 있습니다.It takes less processing time for the 데이터베이스 엔진Database Engine to resolve object names if it does not have to search multiple schemas. 또한 사용 권한 및 스키마를 지정 하지 않고 개체를 만들 때 할당 되는 사용자의 기본 스키마로 인 한 액세스 문제를 방지 합니다.It also prevents permission and access problems caused by a user’s default schema being assigned when objects are created without specifying the schema.

  • WHERE 및 JOIN 절에서 지정한 열을 함수로 묶지 않도록 합니다.Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. 함수로 묶을 경우 열이 비결정적 열이 되어 쿼리 프로세서에서 인덱스를 사용할 수 없습니다.Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

  • 대량의 데이터 행을 반환하는 SELECT 문에서는 스칼라 함수를 사용하지 않도록 합니다.Avoid using scalar functions in SELECT statements that return many rows of data. 스칼라 함수는 모든 행에 적용되어야 하므로 행 기반 처리와 비슷한 동작이 발생하여 성능이 저하됩니다.Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.

  • 사용 하지 못하도록 SELECT *합니다.Avoid the use of SELECT *. 대신 필요한 열 이름을 지정합니다.Instead, specify the required column names. 이렇게 하면 프로시저 실행을 중지하는 몇몇 데이터베이스 엔진Database Engine 오류를 방지할 수 있습니다.This can prevent some 데이터베이스 엔진Database Engine errors that stop procedure execution. 예를 들어 한 SELECT * 수까지 12 열 테이블에서 데이터를 반환 하 고 다음 12 열 임시 테이블에 해당 데이터를 삽입 하는 문이 성공 또는 두 테이블의 열 순서를 변경 합니다.For example, a SELECT * statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table succeeds until the number or order of columns in either table is changed.

  • 너무 많은 데이터를 처리하거나 반환하지 않도록 합니다.Avoid processing or returning too much data. 되도록 프로시저 코드의 앞 부분에서 결과를 좁혀 해당 프로시저에서 수행하는 이후의 작업은 가능하면 가장 적은 데이터 집합을 사용하여 완료되도록 합니다.Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. 꼭 필요한 데이터를 클라이언트 응용 프로그램에 보낼 수 있습니다.Send just the essential data to the client application. 이렇게 하면 네트워크를 통해 불필요한 데이터를 보내 클라이언트 응용 프로그램에서 필요 없이 많은 결과 집합을 처리하지 않아도 되므로 효율적입니다.It is more efficient than sending extra data across the network and forcing the client application to work through unnecessarily large result sets.

  • BEGIN/COMMIT TRANSACTION을 사용 하 여 명시적 트랜잭션을 사용 하 고 트랜잭션이 되도록 짧게 유지 합니다.Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible. 트랜잭션이 길면 레코드 잠금 시간이 길어지고 교착 상태가 발생할 확률이 높아집니다.Longer transactions mean longer record locking and a greater potential for deadlocking.

  • 프로시저 내의 오류 처리에 Transact-SQLTransact-SQL TRY…CATCH 기능을 사용합니다.Use the Transact-SQLTransact-SQL TRY…CATCH feature for error handling inside a procedure. TRY…CATCH는 전체 Transact-SQLTransact-SQL 문 블록을 캡슐화할 수 있습니다.TRY…CATCH can encapsulate an entire block of Transact-SQLTransact-SQL statements. 따라서 성능 오버헤드가 줄어들 뿐만 아니라 대폭 줄어든 프로그래밍과 함께 오류 보고가 더욱 정확해집니다.This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming.

  • 프로시저 본문에서 CREATE TABLE 또는 ALTER TABLE Transact-SQLTransact-SQL 문이 참조하는 모든 테이블 열에 DEFAULT 키워드를 사용합니다.Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQLTransact-SQL statements in the body of the procedure. 이렇게 하면 null 값을 허용 하지 않는 열에 NULL이 전달 됩니다.This prevents passing NULL to columns that do not allow null values.

  • 임시 테이블의 각 열에 NULL 또는 NOT NULL을 사용합니다.Use NULL or NOT NULL for each column in a temporary table. ANSI_DFLT_ON과 ANSI_DFLT_OFF 옵션은 CREATE TABLE 또는 ALTER TABLE 문에 NULL 또는 NOT NULL 특성이 지정되지 않은 경우 데이터베이스 엔진Database Engine에서 열에 이러한 특성을 할당하는 방식을 제어합니다.The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the 데이터베이스 엔진Database Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. 프로시저를 만든 연결과는 이 옵션 설정이 다른 프로시저를 연결이 실행하면 두 번째 연결에 만들어진 테이블의 열은 Null 허용 여부가 달라질 수 있으며 다른 동작을 나타낼 수 있습니다.If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. 각 열에 NULL 또는 NOT NULL을 명시적으로 지정하면 프로시저를 실행하는 모든 연결에 대해 Null 허용 여부가 동일한 임시 테이블이 만들어집니다.If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.

  • Null 값을 변환하는 수정 문을 사용하고 Null 값을 포함하는 행을 쿼리에서 제거하는 논리를 포함합니다.Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. 주의에 있는 Transact-SQLTransact-SQL, NULL 비어 또는 "없음"을 의미 합니다.Be aware that in Transact-SQLTransact-SQL, NULL is not an empty or "nothing" value. 알 수 없는 값에 대한 자리 표시자를 나타냅니다. 이 NULL은 특히 결과 집합을 쿼리하거나 AGGREGATE 함수를 사용할 때 예기치 않은 동작을 야기할 수 있습니다.It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions.

  • 고유 값에 대한 특별한 요구 사항이 있지 않으면 UNION 또는 OR 연산자 대신 UNION ALL 연산자를 사용합니다.Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. UNION ALL 연산자를 사용할 경우 중복되는 항목이 결과 집합에서 필터링되지 않으므로 처리 오버헤드가 줄어듭니다.The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

일반적인 주의 사항General Remarks

프로시저에는 미리 정의된 최대 크기가 없습니다.There is no predefined maximum size of a procedure.

사용자 정의 변수는 프로시저에 지정 된 수 또는 시스템 변수를 같은 @@SPID합니다.Variables specified in the procedure can be user-defined or system variables, such as @@SPID.

프로시저를 처음 실행하면 데이터 검색을 위한 최적 액세스 계획을 결정하기 위해 프로시저가 컴파일됩니다.When a procedure is executed for the first time, it is compiled to determine an optimal access plan to retrieve the data. 이후에 프로시저를 실행할 때는 이미 생성된 계획이 데이터베이스 엔진Database Engine의 계획 캐시에 남아 있을 경우 해당 계획을 다시 사용할 수 있습니다.Subsequent executions of the procedure may reuse the plan already generated if it still remains in the plan cache of the 데이터베이스 엔진Database Engine.

SQL ServerSQL Server 시작 시 하나 이상의 프로시저를 자동으로 실행할 수 있습니다.One or more procedures can execute automatically when SQL ServerSQL Server starts. 절차는 시스템 관리자가 만들어야는 마스터 데이터베이스에 있으며 실행는 sysadmin 백그라운드 프로세스로 고정된 서버 역할입니다.The procedures must be created by the system administrator in the master database and executed under the sysadmin fixed server role as a background process. 프로시저에 입력 또는 출력 매개 변수는 사용할 수 없습니다.The procedures cannot have any input or output parameters. 자세한 내용은 참조 저장 프로시저를 실행할합니다.For more information, see Execute a Stored Procedure.

프로시저가 다른 프로시저를 호출하거나 CRL 루틴, 형식 또는 집계를 참조하여 관리 코드를 실행하는 경우에 프로시저가 중첩됩니다.Procedures are nested when one procedure call another or executes managed code by referencing a CLR routine, type, or aggregate. 프로시저와 관리 코드 참조는 최대 32 수준까지 중첩될 수 있습니다.Procedures and managed code references can be nested up to 32 levels. 호출된 프로시저나 관리 코드 참조의 실행이 시작되면 중첩 수준이 하나 증가하고 호출된 프로시저나 관리 참조 코드의 실행이 끝나면 중첩 수준이 하나 감소합니다.The nesting level increases by one when the called procedure or managed code reference begins execution and decreases by one when the called procedure or managed code reference completes execution. 관리 코드 내에서 호출된 메서드는 중첩 수준 제한에 따라 계산되지 않습니다.Methods invoked from within the managed code do not count against the nesting level limit. 그러나 CLR 저장 프로시저가 SQL Server 관리 공급자를 통해 데이터 액세스 작업을 수행하면 관리 코드에서 SQL로의 전환에 추가적인 중첩 수준이 더해집니다.However, when a CLR stored procedure performs data access operations through the SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL.

최대 중첩 수준을 초과하려고 시도하면 전체 호출 체인이 끊어집니다.Attempting to exceed the maximum nesting level causes the entire calling chain to fail. 사용할 수는 @@NESTLEVEL 함수를 현재 저장된 프로시저 실행의 중첩 수준을 반환 합니다.You can use the @@NESTLEVEL function to return the nesting level of the current stored procedure execution.

상호 운용성Interoperability

데이터베이스 엔진Database Engine에서는 Transact-SQLTransact-SQL 프로시저를 만들거나 수정할 때 SET QUOTED_IDENTIFIER와 SET ANSI_NULLS의 설정을 저장합니다.The 데이터베이스 엔진Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQLTransact-SQL procedure is created or modified. 프로시저를 실행할 때는 이러한 원래 설정이 사용됩니다.These original settings are used when the procedure is executed. 따라서 프로시저 실행 시 SET QUOTED_IDENTIFIER와 SET ANSI_NULLS에 대한 클라이언트 세션 설정은 모두 무시됩니다.Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.

SET ARITHABORT, SET ANSI_WARNINGS, SET ANSI_PADDINGS 등의 기타 SET 옵션은 프로시저를 만들거나 수정할 때 저장되지 않습니다.Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a procedure is created or modified. 프로시저의 논리가 특정 설정에 따라 좌우될 경우 적합한 설정을 위해 프로시저 시작 부분에 SET 문을 포함합니다.If the logic of the procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. 프로시저에서 SET 문을 실행할 경우 해당 설정은 프로시저의 실행이 완료될 때까지만 유지됩니다.When a SET statement is executed from a procedure, the setting remains in effect only until the procedure has finished running. 그런 다음 프로시저가 호출되었을 때의 값으로 설정이 복원됩니다.The setting is then restored to the value the procedure had when it was called. 따라서 각 클라이언트는 프로시저의 논리에 영향을 주지 않고 원하는 옵션을 설정할 수 있습니다.This enables individual clients to set the options they want without affecting the logic of the procedure.

SET SHOWPLAN_TEXT와 SET SHOWPLAN_ALL을 제외한 모든 SET 문을 프로시저 내에 지정할 수 있습니다.Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. 이러한 문은 일괄 처리에서 유일한 문이어야 합니다.These must be the only statements in the batch. 선택된 SET 옵션은 프로시저가 실행되는 동안만 유지되고 다시 원래 설정으로 돌아갑니다.The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.

참고

프로시저 또는 사용자 정의 함수에 매개 변수를 전달할 때 또는 일괄 처리 문에서 변수를 선언하고 설정할 때 SET ANSI_WARNINGS는 인식되지 않습니다.SET ANSI_WARNINGS is not honored when passing parameters in a procedure, user-defined function, or when declaring and setting variables in a batch statement. 예를 들어, 변수로 정의 되 면 char(3) 하 고 다음 값으로 설정 된 3 자 보다 큰 데이터 잘리고 정의 된 크기는 INSERT 또는 UPDATE 문이 성공 합니다.For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

제한 사항Limitations and Restrictions

CREATE PROCEDURE 문은 단일 일괄 처리에서 다른 Transact-SQLTransact-SQL 문과 함께 사용할 수 없습니다.The CREATE PROCEDURE statement cannot be combined with other Transact-SQLTransact-SQL statements in a single batch.

다음 문은 저장 프로시저의 본문 어디에서도 사용할 수 없습니다.The following statements cannot be used anywhere in the body of a stored procedure.

CREATE AGGREGATECREATE AGGREGATE CREATE SCHEMACREATE SCHEMA SET SHOWPLAN_TEXTSET SHOWPLAN_TEXT
CREATE DEFAULTCREATE DEFAULT CREATE 또는 ALTER TRIGGERCREATE or ALTER TRIGGER SET SHOWPLAN_XMLSET SHOWPLAN_XML
CREATE 또는 ALTER FUNCTIONCREATE or ALTER FUNCTION CREATE 또는 ALTER VIEWCREATE or ALTER VIEW 사용 하 여 database_nameUSE database_name
CREATE 또는 ALTER PROCEDURECREATE or ALTER PROCEDURE SET PARSEONLYSET PARSEONLY
CREATE RULECREATE RULE SET SHOWPLAN_ALLSET SHOWPLAN_ALL

프로시저는 아직 존재하지 않는 테이블을 참조할 수 있습니다.A procedure can reference tables that do not yet exist. 프로시저를 만들 때는 구문 검사만 수행되며At creation time, only syntax checking is performed. 처음 실행할 때까지는 프로시저가 컴파일되지 않습니다.The procedure is not compiled until it is executed for the first time. 컴파일 중에만 프로시저에서 참조되는 모든 개체가 확인됩니다.Only during compilation are all objects referenced in the procedure resolved. 존재 하지 않는 테이블을 참조 하는 올바른 구문의 프로시저를 성공적으로 만들 수 있습니다 따라서 그러나 참조 된 테이블이 존재 하지 않는 경우 프로시저 실행 시 실패 합니다.Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure fails at execution time if the referenced tables do not exist.

프로시저 실행 시 매개 변수의 기본값이나 매개 변수로 전달되는 값으로 함수 이름을 지정할 수 없습니다.You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure. 그러나 다음 예와 같이 함수를 변수로 전달할 수 있습니다.However, you can pass a function as a variable as shown in the following example.

-- Passing the function value as a variable.  
DECLARE @CheckDate datetime = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;   
GO  

프로시저가 SQL ServerSQL Server의 원격 인스턴스에서 변경 작업을 수행하면 이러한 변경 내용은 롤백될 수 없습니다.If the procedure makes changes on a remote instance of SQL ServerSQL Server, the changes cannot be rolled back. 원격 프로시저는 트랜잭션에 포함되지 않습니다.Remote procedures do not take part in transactions.

데이터베이스 엔진Database Engine이 .NET Framework에서 오버로드될 때 올바른 메서드를 참조하려면 EXTERNAL NAME 절에 지정된 메서드에 다음과 같은 특징이 있어야 합니다.For the 데이터베이스 엔진Database Engine to reference the correct method when it is overloaded in the .NET Framework, the method specified in the EXTERNAL NAME clause must have the following characteristics:

  • 정적 메서드로 선언되었습니다.Be declared as a static method.

  • 프로시저의 매개 변수 개수와 동일한 개수의 매개 변수를 받습니다.Receive the same number of parameters as the number of parameters of the procedure.

  • SQL ServerSQL Server 프로시저에 있는 해당 매개 변수의 데이터 형식과 호환되는 매개 변수 형식을 사용합니다.Use parameter types that are compatible with the data types of the corresponding parameters of the SQL ServerSQL Server procedure. 일치 하는 방법에 대 한 내용은 SQL ServerSQL Server 데이터 형식에 .NET Framework.NET Framework 데이터 형식을 참조 CLR 매개 변수 데이터 매핑합니다.For information about matching SQL ServerSQL Server data types to the .NET Framework.NET Framework data types, see Mapping CLR Parameter Data.

메타데이터Metadata

다음 테이블에서는 저장 프로시저에 대한 정보를 반환하는 데 사용할 수 있는 카탈로그 뷰 및 동적 관리 뷰를 나열합니다.The following table lists the catalog views and dynamic management views that you can use to return information about stored procedures.

보기View DescriptionDescription
sys.sql_modulessys.sql_modules Transact-SQLTransact-SQL 프로시저의 정의를 반환합니다.Returns the definition of a Transact-SQLTransact-SQL procedure. 사용 하 여 암호화 옵션을 사용 하 여 만든 프로시저의 텍스트를 볼 수 없습니다는 sys.sql_modules 카탈로그 뷰에 있습니다.The text of a procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view.
sys.assembly_modulessys.assembly_modules CLR 프로시저 정보를 반환합니다.Returns information about a CLR procedure.
sys.parameterssys.parameters 프로시저에서 정의된 매개 변수 정보를 반환합니다.Returns information about the parameters that are defined in a procedure
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entitiessys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities 프로시저에서 참조하는 개체를 반환합니다.Returns the objects that are referenced by a procedure.

컴파일된 프로시저의 크기를 예측하려면 다음과 같은 성능 모니터 카운터를 사용합니다.To estimate the size of a compiled procedure, use the following Performance Monitor Counters.

성능 모니터 개체 이름Performance Monitor object name 성능 모니터 카운터 이름Performance Monitor Counter name
SQLServer: Plan Cache 개체SQLServer: Plan Cache Object Cache Hit RatioCache Hit Ratio
Cache PagesCache Pages
Cache Object CountsCache Object Counts

* 이 카운터는 임시 Transact-SQLTransact-SQL, 준비된 Transact-SQLTransact-SQL, 프로시저, 트리거 등 여러 종류의 캐시 개체에서 사용할 수 있습니다.*These counters are available for various categories of cache objects including ad hoc Transact-SQLTransact-SQL, prepared Transact-SQLTransact-SQL, procedures, triggers, and so on. 자세한 내용은 참조 SQL Server, Plan Cache 개체합니다.For more information, see SQL Server, Plan Cache Object.

보안Security

PermissionsPermissions

필요한 CREATE PROCEDURE 데이터베이스의 권한 및 ALTER 프로시저를 만들고 또는의 멤버 자격이 필요 스키마에 대 한 권한이 db_ddladmin 고정된 데이터베이스 역할입니다.Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.

CLR 저장 프로시저에 대 한 EXTERNAL NAME 절에 참조 된 어셈블리의 소유권을 요구 또는 참조 해당 어셈블리에 대 한 권한이 있습니다.For CLR stored procedures, requires ownership of the assembly referenced in the EXTERNAL NAME clause, or REFERENCES permission on that assembly.

CREATE PROCEDURE 및 메모리 액세스에 최적화 된 테이블 CREATE PROCEDURE and Memory-Optimized Tables

메모리 액세스에 최적화 된 테이블을 기존 및 고유 하 게 컴파일된 저장된 프로시저를 통해 액세스할 수 있습니다.Memory-optimized tables can be accessed through both traditional and natively compiled stored procedures. 기본 절차는 대부분의 경우에 더욱 효율적으로.Native procedures are in most cases the more efficient way. 자세한 내용은 참조 Natively Compiled Stored Procedures합니다.For more information, see Natively Compiled Stored Procedures.

다음 샘플에서는 메모리 액세스에 최적화 된 테이블에 액세스 하는 고유 하 게 컴파일된 저장된 프로시저를 만드는 방법을 보여 줍니다. dbo.Departments:The following sample shows how to create a natively compiled stored procedure that accesses a memory-optimized table dbo.Departments:

CREATE PROCEDURE dbo.usp_add_kitchen @dept_id int, @kitchen_count int NOT NULL  
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  

  UPDATE dbo.Departments  
  SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count  
  WHERE id = @dept_id  
END;  
GO  

NATIVE_COMPILATION 없이 만든 프로시저는 고유하게 컴파일된 저장 프로시저로 변경할 수 없습니다.A procedure created without NATIVE_COMPILATION cannot be altered to a natively compiled stored procedure.

고유 하 게 컴파일된 저장된 프로시저의 프로그래밍 기능에 대 한 설명은에 대 한 쿼리 노출 영역을 지원 하 고 연산자 참조 고유 하 게 컴파일된 T-SQL 모듈의 지원 되는 기능합니다.For a discussion of programmability in natively compiled stored procedures, supported query surface area, and operators see Supported Features for Natively Compiled T-SQL Modules.

Examples

범주Category 중요한 구문 요소Featured syntax elements
기본 구문Basic Syntax CREATE PROCEDURECREATE PROCEDURE
매개 변수 전달Passing parameters @parameter
  • = 기본값   • = default
  • 출력   • OUTPUT
  • 테이블 반환 매개 변수 형식   • table-valued parameter type
  • CURSOR VARYING   • CURSOR VARYING
저장된 프로시저를 사용 하 여 데이터 수정Modifying data by using a stored procedure UPDATEUPDATE
오류 처리Error Handling TRY…CATCHTRY…CATCH
프로시저 정의 난독 처리Obfuscating the procedure definition WITH ENCRYPTIONWITH ENCRYPTION
프로시저의 다시 컴파일 강제 적용Forcing the Procedure to Recompile WITH RECOMPILEWITH RECOMPILE
보안 컨텍스트를 설정합니다.Setting the Security Context EXECUTE ASEXECUTE AS

기본 구문 Basic Syntax

이 섹션의 예에서는 최소 필수 구문을 사용하여 CREATE PROCEDURE 문의 기본 기능을 보여 줍니다.Examples in this section demonstrate the basic functionality of the CREATE PROCEDURE statement using the minimum required syntax.

1.A. 단순 Transact-SQL 프로시저 만들기Creating a simple Transact-SQL procedure

다음 예에서는 AdventureWorks2012AdventureWorks2012 데이터베이스의 뷰에서 모든 직원(성과 이름 제공), 직함 및 부서 이름을 반환하는 저장 프로시저를 만듭니다.The following example creates a stored procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view in the AdventureWorks2012AdventureWorks2012 database. 이 프로시저는 매개 변수를 사용하지 않습니다.This procedure does not use any parameters. 다음 예에서는 세 가지의 프로시저 실행 방법에 대해 설명합니다.The example then demonstrates three methods of executing the procedure.

CREATE PROCEDURE HumanResources.uspGetAllEmployees  
AS  
    SET NOCOUNT ON;  
    SELECT LastName, FirstName, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment;  
GO  

SELECT * FROM HumanResources.vEmployeeDepartment;  

uspGetEmployees 다음과 같은 방법으로 프로시저를 실행할 수 있습니다.The uspGetEmployees procedure can be executed in the following ways:

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

2.B. 둘 이상의 결과 집합 반환Returning more than one result set

다음 프로시저는 두 개의 결과 집합을 반환합니다.The following procedure returns two result sets.

CREATE PROCEDURE dbo.uspMultipleResults   
AS  
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;  
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;  
GO  

3.C. CLR 저장 프로시저 만들기Creating a CLR stored procedure

다음 예제에서는 GetPhotoFromDB 참조 하는 프로시저는 GetPhotoFromDB 의 메서드는 LargeObjectBinary 클래스에 HandlingLOBUsingCLR 어셈블리입니다.The following example creates the GetPhotoFromDB procedure that references the GetPhotoFromDB method of the LargeObjectBinary class in the HandlingLOBUsingCLR assembly. 프로시저를 만들기 전에 HandlingLOBUsingCLR 어셈블리가 로컬 데이터베이스에 등록 됩니다.Before the procedure is created, the HandlingLOBUsingCLR assembly is registered in the local database.

적용 대상: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017, SQL 데이터베이스SQL Database (에서 생성 된 어셈블리를 사용 하는 경우 assembly_bits 합니다.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL 데이터베이스SQL Database (if using an assembly created from assembly_bits.

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  

매개 변수 전달 Passing Parameters

이 섹션의 예에서는 입력 및 출력 매개 변수를 통해 값을 저장 프로시저로 전달 및 저장 프로시저에서 값을 전달하는 방법을 보여 줍니다.Examples in this section demonstrate how to use input and output parameters to pass values to and from a stored procedure.

4.D. 입력 매개 변수가 있는 프로시저 만들기Creating a procedure with input parameters

다음 예에서는 직원의 성과 이름에 대한 값을 전달하여 특정 직원 정보를 반환하는 저장 프로시저를 만듭니다.The following example creates a stored procedure that returns information for a specific employee by passing values for the employee's first name and last name. 이 프로시저는 전달된 매개 변수와 정확히 일치하는 항목만 받습니다.This procedure accepts only exact matches for the parameters passed.

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, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment  
    WHERE FirstName = @FirstName AND LastName = @LastName;  
GO  

uspGetEmployees 다음과 같은 방법으로 프로시저를 실행할 수 있습니다.The uspGetEmployees procedure can be executed in the following ways:

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';  

5.E. 프로시저에 와일드카드 매개 변수 사용Using a procedure with wildcard parameters

다음 예에서는 직원의 성과 이름에 대한 전체 또는 일부 값을 전달하여 직원 정보를 반환하는 저장 프로시저를 만듭니다.The following example creates a stored procedure that returns information for employees by passing full or partial values for the employee's first name and last name. 이 프로시저 패턴 전달 된 매개 변수와 일치 또는 사용 하 여 미리 설정 된 기본값을 제공 하지 않으면 (문자로 시작 하는 성이 D).This procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter D).

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, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment  
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;  

uspGetEmployees2 프로시저는 여러 가지 조합으로 실행할 수 있습니다.The uspGetEmployees2 procedure can be executed in many combinations. 다음에 표시된 것은 이 중 일부입니다.Only a few possible combinations are shown here.

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%';  

6.F. OUTPUT 매개 변수 사용Using OUTPUT parameters

다음 예에서는 uspGetList 프로시저를 만듭니다.The following example creates the uspGetList procedure. 이 프로시저에서는 가격이 지정한 금액을 초과하지 않는 제품 목록을 반환합니다.This procedures returns a list of products that have prices that do not exceed a specified amount. 다음 예에서는 여러 SELECT 문과 여러 OUTPUT 매개 변수의 사용 방법을 보여 줍니다.The example shows using multiple SELECT statements and multiple OUTPUT parameters. OUTPUT 매개 변수는 프로시저를 실행하는 동안 외부 프로시저, 일괄 처리 또는 둘 이상의 Transact-SQLTransact-SQL 문이 값 집합을 액세스하도록 허용합니다.OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQLTransact-SQL statement to access a value set during the procedure execution.

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 WorksAdventure Works 제품(자전거) 목록을 반환합니다.Execute uspGetList to return a list of Adventure WorksAdventure Works products (Bikes) that cost less than $700. OUTPUT 매개 변수 @Cost@ComparePrices 흐름 제어 언어와 함께 메시지를 반환 하는 데 사용 되는 메시지 창.The OUTPUT parameters @Cost and @ComparePrices are used with control-of-flow language to return a message in the Messages window.

참고

프로시저가 만들어질 때뿐 아니라 변수가 사용될 때도 OUTPUT 변수를 정의해야 합니다.The OUTPUT variable must be defined when the procedure is created and also when the variable is used. 매개 변수 이름과 변수 이름은; 일치 하지 않아도 그러나 데이터 형식 및 매개 변수 위치가 일치 해야 하지 않는 한 @ListPrice = 변수 사용 됩니다.The parameter name and variable name do not have to match; however, the data type and parameter positioning must match, unless @ListPrice = variable is used.

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)))+'.';  

다음은 결과 집합의 일부입니다.Here is the partial result set:

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.

7.G. 테이블 반환 매개 변수 사용Using a Table-Valued Parameter

다음 예에서는 테이블 반환 매개 변수 유형을 사용하여 테이블에 여러 행을 삽입합니다.The following example uses a table-valued parameter type to insert multiple rows into a table. 다음 예에서는 매개 변수 유형을 만들고, 해당 형식을 참조하는 테이블 변수를 선언하며, 매개 변수 목록을 채운 다음 저장 프로시저에 값을 전달하는 방법을 보여 줍니다.The example creates the parameter type, declares a table variable to reference it, fills the parameter list, and then passes the values to a stored procedure. 저장 프로시저는 해당 값을 사용하여 테이블에 여러 행을 삽입합니다.The stored procedure uses the values to insert multiple rows into a table.

/* Create a table type. */  
CREATE TYPE LocationTableType AS TABLE   
( LocationName VARCHAR(50)  
, CostRate INT );  
GO  

/* Create a procedure to receive data for the table-valued parameter. */  
CREATE PROCEDURE usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO [AdventureWorks2012].[Production].[Location]  
           ([Name]  
           ,[CostRate]  
           ,[Availability]  
           ,[ModifiedDate])  
        SELECT *, 0, GETDATE()  
        FROM  @TVP;  
GO  

/* Declare a variable that references the type. */  
DECLARE @LocationTVP   
AS LocationTableType;  

/* Add data to the table variable. */  
INSERT INTO @LocationTVP (LocationName, CostRate)  
    SELECT [Name], 0.00  
    FROM   
    [AdventureWorks2012].[Person].[StateProvince];  

/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO  
8.H. OUTPUT 커서 매개 변수 사용Using an OUTPUT cursor parameter

다음 예에서는 프로시저에서 로컬로 사용되는 커서를 호출한 일괄 처리, 프로시저 또는 트리거에 다시 전달하는 OUTPUT 커서 매개 변수를 사용합니다.The following example uses the OUTPUT cursor parameter to pass a cursor that is local to a procedure back to the calling batch, procedure, or trigger.

먼저 커서를 선언하여 Currency 테이블에서 여는 프로시저를 만듭니다.First, create the procedure that declares and then opens a cursor on the Currency table:

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  

다음으로 로컬 커서 변수를 선언하는 일괄 처리를 실행하고 지역 변수에 커서를 할당하는 프로시저를 실행한 다음 커서에서 행을 인출합니다.Next, run a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

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

저장 프로시저를 사용 하 여 데이터 수정 Modifying Data by using a Stored Procedure

이 섹션의 예에서는 프로시저 정의에 DML(데이터 조작 언어) 문을 포함해 테이블 또는 뷰에 있는 데이터를 삽입하거나 수정하는 방법을 보여 줍니다.Examples in this section demonstrate how to insert or modify data in tables or views by including a Data Manipulation Language (DML) statement in the definition of the procedure.

9.I. 저장 프로시저에 UPDATE 사용Using UPDATE in a stored procedure

다음 예에서는 저장 프로시저에 UPDATE 문을 사용합니다.The following example uses an UPDATE statement in a stored procedure. 이 프로시저에서는 @NewHours라는 입력 매개 변수 하나와 @RowCount라는 출력 매개 변수 하나를 사용합니다.The procedure takes one input parameter, @NewHours and one output parameter @RowCount. @NewHours 매개 변수 값은 UPDATE 문이 열을 업데이트 사용 VacationHours 표에 HumanResources.Employee합니다.The @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. @RowCount 출력 매개 변수는 영향을 받는 행 수를 지역 변수에 반환하는 데 사용됩니다.The @RowCount output parameter is used to return the number of rows affected to a local variable. CASE 식은 SET 절에서 VacationHours에 설정되는 값을 조건에 따라 결정하는 데 사용됩니다.A CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours. 시간당 급여를 받는 직원(SalariedFlag = 0)의 경우 현재 시간 수에 VacationHours에 지정된 값을 더한 값으로 @NewHours가 설정되고, 그 외의 경우에는 VacationHours에 지정된 값으로 @NewHours가 설정됩니다.When the employee is paid hourly (SalariedFlag = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours; otherwise, VacationHours is set to the value specified in @NewHours.

CREATE PROCEDURE HumanResources.Update_VacationHours  
@NewHours smallint  
AS   
SET NOCOUNT ON;  
UPDATE HumanResources.Employee  
SET VacationHours =   
    ( CASE  
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours  
         ELSE @NewHours  
       END  
    )  
WHERE CurrentFlag = 1;  
GO  

EXEC HumanResources.Update_VacationHours 40;  

오류 처리 Error Handling

이 섹션의 예에서는 저장 프로시저가 실행될 때 발생할 수 있는 오류를 처리하는 방법을 보여 줍니다.Examples in this section demonstrate methods to handle errors that might occur when the stored procedure is executed.

10.J. TRY…CATCH 사용Using TRY…CATCH

다음 예에서는 TRY…CATCH 구문을 통해 저장 프로시저를 실행하는 동안 발생한 오류 정보를 반환합니다.The following example using the TRY…CATCH construct to return error information caught during the execution of a stored procedure.

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )  
AS  
SET NOCOUNT ON;  
BEGIN TRY  
   BEGIN TRANSACTION   
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.  
   DELETE FROM Production.WorkOrderRouting  
   WHERE WorkOrderID = @WorkOrderID;  

   -- Delete the rows from the parent table, WorkOrder, for the specified work order.  
   DELETE FROM Production.WorkOrder  
   WHERE WorkOrderID = @WorkOrderID;  

   COMMIT  

END TRY  
BEGIN CATCH  
  -- Determine if an error occurred.  
  IF @@TRANCOUNT > 0  
     ROLLBACK  

  -- Return the error information.  
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;  
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();  
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);  
END CATCH;  

GO  
EXEC Production.uspDeleteWorkOrder 13;  

/* Intentionally generate an error by reversing the order in which rows 
   are deleted from the parent and child tables. This change does not 
   cause an error when the procedure definition is altered, but produces 
   an error when the procedure is executed.  
*/  
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )  
AS  

BEGIN TRY  
   BEGIN TRANSACTION   
      -- Delete the rows from the parent table, WorkOrder, for the specified work order.  
   DELETE FROM Production.WorkOrder  
   WHERE WorkOrderID = @WorkOrderID;  

   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.  
   DELETE FROM Production.WorkOrderRouting  
   WHERE WorkOrderID = @WorkOrderID;  

   COMMIT TRANSACTION  

END TRY  
BEGIN CATCH  
  -- Determine if an error occurred.  
  IF @@TRANCOUNT > 0  
     ROLLBACK TRANSACTION  

  -- Return the error information.  
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;  
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();  
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);  
END CATCH;  
GO  
-- Execute the altered procedure.  
EXEC Production.uspDeleteWorkOrder 15;  

DROP PROCEDURE Production.uspDeleteWorkOrder;  

프로시저 정의 난독 처리 Obfuscating the Procedure Definition

이 섹션의 예에서는 저장 프로시저 정의를 난독 처리하는 방법을 보여 줍니다.Examples in this section show how to obfuscate the definition of the stored procedure.

11.K. WITH ENCRYPTION 옵션 사용Using the WITH ENCRYPTION option

다음 예에서는 HumanResources.uspEncryptThis 프로시저를 만듭니다.The following example creates the HumanResources.uspEncryptThis procedure.

적용 대상: SQL Server 2008SQL Server 2008 통해 SQL Server 2017SQL Server 2017, SQL 데이터베이스입니다.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL Database.

CREATE PROCEDURE HumanResources.uspEncryptThis  
WITH ENCRYPTION  
AS  
    SET NOCOUNT ON;  
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, 
        VacationHours, SickLeaveHours   
    FROM HumanResources.Employee;  
GO  

WITH ENCRYPTION 옵션 난독 처리 프로시저의 정의가 시스템 카탈로그 또는 메타 데이터를 사용 하 여 작동 하는 경우 다음 예에 표시 된 것 처럼 합니다.The WITH ENCRYPTION option obfuscates the definition of the procedure when querying the system catalog or using metadata functions, as shown by the following examples.

Run sp_helptext:Run sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';  

결과 집합은 다음과 같습니다.Here is the result set.

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

직접 쿼리 하는 sys.sql_modules 카탈로그 뷰:Directly query the sys.sql_modules catalog view:

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

결과 집합은 다음과 같습니다.Here is the result set.

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

프로시저의 다시 컴파일 강제 적용 Forcing the Procedure to Recompile

이 섹션의 예에서는 WITH RECOMPILE 절을 사용하여 프로시저를 실행할 때마다 강제로 다시 컴파일되도록 합니다.Examples in this section use the WITH RECOMPILE clause to force the procedure to recompile every time it is executed.

12.L. WITH RECOMPILE 옵션 사용Using the WITH RECOMPILE option

WITH RECOMPILE 절은 프로시저에 제공 된 매개 변수는 일반적인 것 이며 및 새로운 실행 계획이 캐시 되거나 메모리에 저장 되지 않아야 하는 경우에 유용 합니다.The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure are not typical, and when a new execution plan should not be cached or stored in memory.

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.BusinessEntityID = pv.BusinessEntityID   
    JOIN Production.Product AS p   
      ON pv.ProductID = p.ProductID  
    WHERE v.Name LIKE @Name;  

보안 컨텍스트를 설정합니다. Setting the Security Context

이 섹션의 예에서는 EXECUTE AS 절을 사용하여 저장 프로시저가 실행되는 보안 컨텍스트를 설정합니다.Examples in this section use the EXECUTE AS clause to set the security context in which the stored procedure executes.

13.M. EXECUTE AS 절 사용Using the EXECUTE AS clause

다음 예에서는 사용 하는 EXECUTE AS 절 프로시저를 실행할 수 있는 보안 컨텍스트를 지정 합니다.The following example shows using the EXECUTE AS clause to specify the security context in which a procedure can be executed. 예제에서는 옵션 CALLER 프로시저를 호출 하는 사용자의 컨텍스트에서 실행 수 있도록 지정 합니다.In the example, the option CALLER specifies that the procedure can be executed in the context of the user that calls it.

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 'Rating',   
      v.ActiveFlag AS Availability  
    FROM Purchasing.Vendor v   
    INNER JOIN Purchasing.ProductVendor pv  
      ON v.BusinessEntityID = pv.BusinessEntityID   
    INNER JOIN Production.Product p  
      ON pv.ProductID = p.ProductID   
    ORDER BY v.Name ASC;  
GO  

14.N. 사용자 지정 권한 집합 만들기Creating custom permission sets

다음 예에서는 EXECUTE AS를 사용하여 데이터베이스 작업에 대한 사용자 지정 권한을 만듭니다.The following example uses EXECUTE AS to create custom permissions for a database operation. TRUNCATE TABLE과 같은 일부 작업에는 부여할 수 있는 사용 권한이 없습니다.Some operations such as TRUNCATE TABLE, do not have grantable permissions. TRUNCATE TABLE 문을 저장 프로시저에 통합하고 테이블 수정 권한을 가진 사용자로 프로시저가 실행되도록 지정하면 테이블 자르기 권한을 프로시저에 대해 EXECUTE 권한을 부여한 사용자에게로 확장할 수 있습니다.By incorporating the TRUNCATE TABLE statement within a stored procedure and specifying that procedure execute as a user that has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the procedure.

CREATE PROCEDURE dbo.TruncateMyTable  
WITH EXECUTE AS SELF  
AS TRUNCATE TABLE MyDB..MyTable;  

예: Azure SQL 데이터 웨어하우스Azure SQL Data Warehouse병렬 데이터 웨어하우스Parallel Data WarehouseExamples: Azure SQL 데이터 웨어하우스Azure SQL Data Warehouse and 병렬 데이터 웨어하우스Parallel Data Warehouse

15.O. SELECT 문을 실행 하는 저장 프로시저 만들기Create a Stored Procedure that runs a SELECT statement

이 예제를 만들고 프로시저를 실행 하기 위한 기본 구문을 보여 줍니다.This example shows the basic syntax for creating and running a procedure. 일괄 처리를 실행할 때 CREATE PROCEDURE에는 첫 번째 문 이어야 합니다.When running a batch, CREATE PROCEDURE must be the first statement. 예를 들어 다음 저장 프로시저를 만들려면에서 AdventureWorksPDW2012AdventureWorksPDW2012먼저 데이터베이스 컨텍스트를 설정 하 고 다음 CREATE PROCEDURE 문을 실행 합니다.For example, to create the following stored procedure in AdventureWorksPDW2012AdventureWorksPDW2012, set the database context first, and then run the CREATE PROCEDURE statement.

-- Uses AdventureWorksDW database  

--Run CREATE PROCEDURE as the first statement in a batch.  
CREATE PROCEDURE Get10TopResellers   
AS   
BEGIN  
    SELECT TOP (10) r.ResellerName, r.AnnualSales  
    FROM DimReseller AS r  
    ORDER BY AnnualSales DESC, ResellerName ASC;  
END  
;  

--Show 10 Top Resellers  
EXEC Get10TopResellers;  

관련 항목:See Also

ALTER PROCEDURE(Transact-SQL) ALTER PROCEDURE (Transact-SQL)
흐름 제어 언어 ( Transact SQL ) Control-of-Flow Language (Transact-SQL)
커서 Cursors
데이터 형식(Transact-SQL) Data Types (Transact-SQL)
DECLARE @local_variable(Transact-SQL) DECLARE @local_variable (Transact-SQL)
DROP procedure( Transact SQL ) DROP PROCEDURE (Transact-SQL)
EXECUTE(Transact-SQL) EXECUTE (Transact-SQL)
실행 AS ( Transact SQL ) EXECUTE AS (Transact-SQL)
저장 프로시저(데이터베이스 엔진) Stored Procedures (Database Engine)
sp_procoption( Transact SQL ) sp_procoption (Transact-SQL)
sp_recompile ( Transact SQL ) sp_recompile (Transact-SQL)
sys.sql_modules(Transact-SQL) sys.sql_modules (Transact-SQL)
sys.parameters(Transact-SQL) sys.parameters (Transact-SQL)
sys.procedures(Transact-SQL) sys.procedures (Transact-SQL)
sys.sql_expression_dependencies(Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sys.assembly_modules(Transact-SQL) sys.assembly_modules (Transact-SQL)
sys.numbered_procedures ( Transact SQL ) sys.numbered_procedures (Transact-SQL)
sys.numbered_procedure_parameters ( Transact SQL ) sys.numbered_procedure_parameters (Transact-SQL)
OBJECT_DEFINITION(Transact-SQL) OBJECT_DEFINITION (Transact-SQL)
저장 프로시저 만들기 Create a Stored Procedure
테이블 반환 매개 변수 사용 ( 데이터베이스 엔진 )를 사용 하 여 Use Table-Valued Parameters (Database Engine)
sys.dm_sql_referenced_entities(Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities(Transact-SQL)sys.dm_sql_referencing_entities (Transact-SQL)