ALTER PROCEDURE(Transact-SQL)

아티클
05/23/2023

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW) Microsoft Fabric의 SQL 분석 엔드포인트 Microsoft Fabric의 웨어하우스

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

Transact-SQL 구문 표기 규칙(Transact-SQL)

구문

-- Syntax for SQL Server and Azure SQL Database
  
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]   
    [ { @parameter_name [ 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 ]

-- Syntax for SQL Server CLR Stored Procedure  
  
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]   
    [ { @parameter_name [ type_schema_name. ] data_type }   
        [ = default ] [ OUT | OUTPUT ] [READONLY]  
    ] [ ,...n ]   
[ WITH EXECUTE AS Clause ]  
AS { EXTERNAL NAME assembly_name.class_name.method_name }  
[;]

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse and Microsoft Fabric
  
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name  
    [ { @parameterdata_type } [= ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [ ; ] [ ,...n ] [ END ] }  
[;]

인수

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

procedure_name
변경할 프로시저의 이름입니다. 프로시저 이름은 식별자에 대한 규칙을 따라야 합니다.

; 수
같은 이름을 가진 여러 개의 프로시저가 하나의 DROP PROCEDURE 문을 사용하여 삭제될 수 있도록 이러한 프로시저를 그룹화하는 데 사용되는 기존의 선택적인 정수입니다.

참고 항목

SQL Server의 이후 버전에서는 이 기능이 제거됩니다. 새 개발 작업에서는 이 기능을 사용하지 않도록 하고, 현재 이 기능을 사용하는 애플리케이션은 수정하세요.

@parameter_name
프로시저의 매개 변수입니다. 매개 변수는 2,100개까지 지정할 수 있습니다.

[ type_schema_name. ] data_type
매개 변수와 매개 변수가 속하는 스키마의 데이터 형식입니다.

데이터 형식 제한 사항에 대한 자세한 내용은 CREATE PROCEDURE(Transact-SQL)를 참조하세요.

VARYING
결과 집합이 출력 매개 변수로 사용되도록 지정합니다. 이 매개 변수는 저장 프로시저에 의해 동적으로 생성될 수 있으며 해당 내용은 여러 가지가 될 수 있습니다. 커서 매개 변수에만 적용됩니다. CLR 프로시저에는 이 옵션이 유효하지 않습니다.

default
매개 변수의 기본값입니다.

OUT | OUTPUT
매개 변수가 반환 매개 변수임을 나타냅니다.

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

RECOMPILE
데이터베이스 엔진이 이 프로시저에 대한 계획을 캐시하지 않고 런타임에 프로시저가 다시 컴파일됨을 나타냅니다.

ENCRYPTION
적용 대상: SQL Server(SQL Server 2008(10.0.x) 이상) 및 Azure SQL Database.

데이터베이스 엔진에서 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
적용 대상: SQL Server 2008(10.0.x) 이상

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를 공급하는 모든 공급업체 이름, 해당 공급업체가 공급하는 제품, 신용 등급 및 사용 가능성을 반환합니다. 이 프로시저를 만든 후 다른 결과 집합을 반환하도록 프로시저를 수정합니다.

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 AdventureWorks2022;  
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)`

참고 항목

CREATE PROCEDURE(Transact-SQL)
DROP PROCEDURE(Transact-SQL)
EXECUTE(Transact-SQL)
EXECUTE AS(Transact-SQL)
EVENTDATA(Transact-SQL)
저장 프로시저(데이터베이스 엔진)
sys.procedures(Transact-SQL)

다음을 통해 공유

ALTER PROCEDURE(Transact-SQL)

구문

인수

일반적인 주의 사항

보안

사용 권한

예제

참고 항목

피드백

추가 리소스