ALTER PROCEDURE(Transact-SQL)
이전에 CREATE PROCEDURE 문을 실행하여 만든 프로시저를 수정합니다. ALTER PROCEDURE는 사용 권한을 변경하지 않으며 종속 저장 프로시저나 트리거에 영향을 주지 않습니다. 그러나 QUOTED_IDENTIFIER 및 ANSI_NULLS에 대한 현재 세션 설정은 저장 프로시저가 수정될 때 저장 프로시저에 포함됩니다. 이 설정이 처음 저장 프로시저를 만들 때 적용한 설정과 다르면 저장 프로시저의 동작은 달라질 수 있습니다.
.gif "항목 링크 아이콘")
구문
--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
지원되는 결과 집합을 출력 매개 변수로 지정합니다. 이 매개 변수는 저장 프로시저에 의해 동적으로 생성되므로 해당 내용은 달라질 수 있습니다. 커서 매개 변수에만 적용됩니다.default
매개 변수의 기본값입니다.OUT | OUTPUT
매개 변수가 반환 매개 변수임을 나타냅니다.READONLY
프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음을 나타냅니다. 매개 변수 형식이 테이블 반환 형식인 경우 READONLY를 지정해야 합니다.RECOMPILE
SQL Server 2005 데이터베이스 엔진이 이 프로시저에 대한 계획을 캐시하지 않고 런타임에 프로시저가 다시 컴파일됨을 나타냅니다.ENCRYPTION
데이터베이스 엔진에서 ALTER PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환됩니다. 변조된 출력은 SQL Server 2005의 카탈로그 뷰 어디에서도 직접 표시되지 않습니다. 시스템 테이블 또는 데이터베이스 파일에 대한 액세스 권한이 없는 사용자는 변조된 텍스트를 검색할 수 없습니다. 그러나 DAC 포트를 통해 시스템 테이블에 액세스하거나 데이터베이스 파일에 직접 액세스할 수 있는 권한을 가진 사용자는 변조된 텍스트를 사용할 수 있습니다. 또한 디버거를 서버 프로세스에 연결할 수 있는 사용자는 런타임에 메모리에서 원래 프로시저를 검색할 수 있습니다. 시스템 메타데이터에 액세스하는 방법은 메타데이터 표시 유형 구성을 참조하십시오.이 옵션을 사용하여 만든 프로시저는 SQL Server 복제의 일부로 게시할 수 없습니다.
CLR(공용 언어 런타임) 저장 프로시저에 대해 이 옵션을 지정할 수 없습니다.
[!참고]
업그레이드하는 동안 데이터베이스 엔진은 sys.sql_modules에 저장된 난독 처리된 주석을 사용하여 프로시저를 다시 만듭니다.
EXECUTE AS
액세스된 후 저장 프로시저를 실행할 보안 컨텍스트를 지정합니다.자세한 내용은 EXECUTE AS 절(Transact-SQL)을 참조하십시오.
FOR REPLICATION
복제용으로 만든 저장 프로시저가 구독자에서 실행될 수 없도록 지정합니다. FOR REPLICATION 옵션을 사용하여 만든 저장 프로시저는 저장 프로시저 필터로 사용되며 복제하는 동안에만 실행됩니다. FOR REPLICATION을 지정하면 매개 변수를 선언할 수 없습니다. FOR REPLICATION을 사용하여 만든 프로시저의 경우 RECOMPILE 옵션이 무시됩니다.AS
프로시저가 수행할 동작입니다.<sql_statement>
프로시저에 포함될 Transact-SQL 문으로서 원하는 형식을 원하는 수만큼 지정합니다. 일부 제한 사항이 적용됩니다. 자세한 내용은 CREATE PROCEDURE(Transact-SQL)의 "<sql_statement> 제한 사항"을 참조하십시오.EXTERNAL NAME assembly_name**.class_name.method_name
CLR 저장 프로시저가 참조할 Microsoft.NET Framework 어셈블리의 메서드를 지정합니다. class_name은 유효한 SQL Server 식별자여야 하며 해당 어셈블리에 클래스로 존재해야 합니다. 클래스에 마침표(.)를 사용하여 네임스페이스 부분을 구분하는 네임스페이스로 한정된 이름이 있는 경우 클래스 이름은 대괄호([]) 또는 따옴표("?"**)를 사용하여 구분되어야 합니다. 지정한 메서드는 해당 클래스의 정적 메서드여야 합니다.[!참고]
기본적으로 SQL Server에서는 CLR 코드를 실행할 수 없습니다. 공용 언어 런타임 모듈을 참조하는 데이터베이스 개체를 생성, 수정 및 삭제할 수 있지만 clr enabled 옵션을 설정할 때까지 SQL Server에서 이러한 참조를 실행할 수 없습니다. 이 옵션을 설정하려면 sp_configure를 사용합니다.
주의
Transact-SQL 저장 프로시저는 CLR 저장 프로시저로 수정될 수 없으며 반대도 마찬가지입니다.
자세한 내용은 CREATE PROCEDURE(Transact-SQL)의 "주의" 섹션을 참조하십시오.
[!참고]
이전 프로시저 정의가 WITH ENCRYPTION 또는 WITH RECOMPILE을 사용하여 만들어진 경우 이러한 옵션은 ALTER PROCEDURE에 포함된 경우에만 활성화됩니다.
사용 권한
프로시저에 대한 ALTER 권한이 필요합니다.
예
다음 예에서는 uspVendorAllInfo 저장 프로시저를 만듭니다. 이 프로시저는 Adventure Works Cycles를 공급하는 모든 공급업체 이름, 해당 공급업체가 공급하는 제품, 신용 등급 및 사용 가능성을 반환합니다. 이 프로시저를 만든 후 다른 결과 집합을 반환하도록 프로시저를 수정합니다.
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
다음 예에서는 EXECUTE AS 옵션 없이 uspVendorAllInfo 저장 프로시저를 변경하여 지정된 제품을 공급하는 공급업체만 반환합니다. LEFT 및 CASE 함수는 결과 집합의 모양을 사용자 지정합니다.
ALTER PROCEDURE Purchasing.uspVendorAllInfo
@Product varchar(25)
AS
SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name',
'Credit 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.VendorID = pv.VendorID
INNER JOIN Production.Product AS p
ON pv.ProductID = p.ProductID
WHERE p.Name LIKE @Product
ORDER BY v.Name ASC;
GO
EXEC Purchasing.uspVendorAllInfo N'LL Crankarm';
GO
결과 집합은 다음과 같습니다.
Vendor Product name Credit rating Availability
-------------------- ------------------- ------------- ------------
Proseware, Inc. LL Crankarm Average No
Vision Cycles, Inc. LL Crankarm Superior Yes
(2 row(s) affected)
참고 항목