ALTER PROCEDURE(Transact-SQL)

아티클
06/18/2013

SQL Server 2012에서 이전에 CREATE PROCEDURE 문을 실행하여 만든 프로시저를 수정합니다.

항목 링크 아이콘 Transact-SQL 구문 표기 규칙(Transact-SQL)

구문

--Transact-SQL Stored Procedure Syntax
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 ]

--CLR Stored Procedure Syntax
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 }
[;]

인수

schema_name
프로시저가 속한 스키마의 이름입니다.
procedure_name
변경할 프로시저의 이름입니다. 프로시저 이름은 식별자에 대한 규칙을 따라야 합니다.
; number
같은 이름을 가진 여러 개의 프로시저가 하나의 DROP PROCEDURE 문을 사용하여 삭제될 수 있도록 이러한 프로시저를 그룹화하는 데 사용되는 기존의 선택적인 정수입니다.

[!참고]

Microsoft SQL Server의 이후 버전에서는 이 기능이 제거됩니다. 새 개발 작업에서는 이 기능을 사용하지 않도록 하고, 현재 이 기능을 사용하는 응용 프로그램은 수정하십시오.
@ parameter
프로시저의 매개 변수입니다. 매개 변수는 2,100개까지 지정할 수 있습니다.
[ type_schema_name**.** ] data_type
매개 변수와 매개 변수가 속하는 스키마의 데이터 형식입니다.

데이터 형식 제한 사항에 대한 자세한 내용은 CREATE PROCEDURE(Transact-SQL)를 참조하십시오.
VARYING
결과 집합이 출력 매개 변수로 사용되도록 지정합니다. 이 매개 변수는 저장 프로시저에 의해 동적으로 생성될 수 있으며 해당 내용은 여러 가지가 될 수 있습니다. 커서 매개 변수에만 적용됩니다. CLR 프로시저에는 이 옵션이 유효하지 않습니다.
default
매개 변수의 기본값입니다.
OUT | OUTPUT
매개 변수가 반환 매개 변수임을 나타냅니다.
READONLY
프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음을 나타냅니다. 매개 변수 형식이 테이블 반환 형식인 경우 READONLY를 지정해야 합니다.
RECOMPILE
데이터베이스 엔진이 이 프로시저에 대한 계획을 캐시하지 않고 런타임에 프로시저가 다시 컴파일됨을 나타냅니다.
ENCRYPTION
데이터베이스 엔진에서 ALTER PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환됩니다. 변조된 출력은 SQL Server의 카탈로그 뷰 어디에서도 직접 표시되지 않습니다. 시스템 테이블 또는 데이터베이스 파일에 대한 액세스 권한이 없는 사용자는 변조된 텍스트를 검색할 수 없습니다. 그러나 DAC 포트를 통해 시스템 테이블에 액세스하거나 데이터베이스 파일에 직접 액세스할 수 있는 권한을 가진 사용자는 변조된 텍스트를 사용할 수 있습니다. 또한 디버거를 서버 프로세스에 연결할 수 있는 사용자는 런타임에 메모리에서 원래 프로시저를 검색할 수 있습니다. 시스템 메타데이터에 액세스하는 방법은 메타데이터 표시 유형 구성을 참조하십시오.

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

CLR(공용 언어 런타임) 저장 프로시저에 대해 이 옵션을 지정할 수 없습니다.

[!참고]

업그레이드하는 동안 데이터베이스 엔진은 sys.sql_modules에 저장된 변조된 주석을 사용하여 프로시저를 다시 만듭니다.
EXECUTE AS
액세스된 후 저장 프로시저를 실행할 보안 컨텍스트를 지정합니다.

자세한 내용은 EXECUTE AS 절(Transact-SQL)을 참조하십시오.
FOR REPLICATION
복제용으로 만든 저장 프로시저가 구독자에서 실행되지 못하도록 지정합니다. FOR REPLICATION 옵션을 사용하여 만든 저장 프로시저는 저장 프로시저 필터로 사용되며 복제하는 동안에만 실행됩니다. FOR REPLICATION을 지정하면 매개 변수를 선언할 수 없습니다. CLR 프로시저에는 이 옵션이 유효하지 않습니다. FOR REPLICATION으로 만든 프로시저의 경우 RECOMPILE 옵션이 무시됩니다.

[!참고]

포함된 데이터베이스에서는 이 옵션을 사용할 수 없습니다.
{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
프로시저 본문을 구성하는 하나 이상의 Transact-SQL 문입니다. 선택적 키워드인 BEGIN과 END를 사용하여 문을 묶을 수 있습니다. 자세한 내용은 CREATE PROCEDURE(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를 사용합니다.

[!참고]

CLR 프로시저는 포함된 데이터베이스에서 지원되지 않습니다.

일반적인 주의

Transact-SQL 저장 프로시저를 CLR 저장 프로시저로 수정할 수 없으며 그 반대의 경우도 마찬가지입니다.

ALTER PROCEDURE는 사용 권한을 변경하지 않으며 종속 저장 프로시저나 트리거에 영향을 주지 않습니다. 그러나 QUOTED_IDENTIFIER 및 ANSI_NULLS에 대한 현재 세션 설정은 저장 프로시저가 수정될 때 저장 프로시저에 포함됩니다. 이 설정이 처음 저장 프로시저를 만들 때 적용한 설정과 다르면 저장 프로시저의 동작은 달라질 수 있습니다.

이전 프로시저 정의가 WITH ENCRYPTION 또는 WITH RECOMPILE을 사용하여 만들어진 경우 이러한 옵션은 ALTER PROCEDURE에 포함된 경우에만 활성화됩니다.

저장 프로시저에 대한 자세한 내용은 CREATE PROCEDURE(Transact-SQL)를 참조하십시오.

보안

사용 권한

프로시저에 대한 ALTER 권한이나 db_ddladmin 고정 데이터베이스 역할의 멤버 자격이 필요합니다.

예

다음 예에서는 uspVendorAllInfo 저장 프로시저를 만듭니다. 이 프로시저는 Adventure Works Cycles를 공급하는 모든 공급업체 이름, 해당 공급업체가 공급하는 제품, 신용 등급 및 사용 가능성을 반환합니다. 이 프로시저를 만든 후 다른 결과 집합을 반환하도록 프로시저를 수정합니다.

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

다음 예에서는 uspVendorAllInfo 저장 프로시저를 변경합니다. EXECUTE AS CALLER 절을 제거하고 지정된 제품을 제공하는 공급업체만 반환하도록 프로시저 본문을 수정합니다. LEFT 및 CASE 함수는 결과 집합의 모양을 사용자 지정합니다.

USE AdventureWorks2012;
GO
ALTER PROCEDURE Purchasing.uspVendorAllInfo
    @Product varchar(25) 
AS
    SET NOCOUNT ON;
    SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name', 
    'Rating' = CASE v.CreditRating 
        WHEN 1 THEN 'Superior'
        WHEN 2 THEN 'Excellent'
        WHEN 3 THEN 'Above average'
        WHEN 4 THEN 'Average'
        WHEN 5 THEN 'Below average'
        ELSE 'No rating'
        END
    , Availability = CASE v.ActiveFlag
        WHEN 1 THEN 'Yes'
        ELSE 'No'
        END
    FROM Purchasing.Vendor AS v 
    INNER JOIN Purchasing.ProductVendor AS pv
      ON v.BusinessEntityID = pv.BusinessEntityID 
    INNER JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID 
    WHERE p.Name LIKE @Product
    ORDER BY v.Name ASC;
GO

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

Vendor Product name Rating Availability

-------------------- ------------- ------- ------------

Proseware, Inc. LL Crankarm Average No

Vision Cycles, Inc. LL Crankarm Superior Yes

(2 row(s) affected)