ALTER PROCEDURE (Transact-SQL)

適用于:SQL Server (所有支援的版本) Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

修改先前在 SQL Server 中執行 CREATE PROCEDURE 語句所建立的程式。

主題連結圖示Transact-SQL 語法慣例 (Transact-SQL)

Syntax

-- 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  
  
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name  
    [ { @parameterdata_type } [= ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [ ; ] [ ,...n ] [ END ] }  
[;]  

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件

引數

schema_name
程序所屬之結構描述的名稱。

procedure_name
要變更之程序的名稱。 程序名稱必須符合 識別碼的規則。

;數量
現有的選擇性整數,用來分組名稱相同的程序,以便能夠利用單一 DROP PROCEDURE 陳述式來同時卸除它們。

注意

未來的 Microsoft SQL Server 版本將移除這項功能。 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。

@parameter_name
程序中的參數。 您最多可以指定 2,100 個參數。

[ type_schema_name. ] data_type
這是參數的資料類型及其所屬的結構描述。

如需資料類型限制的相關資訊,請參閱 CREATE PROCEDURE (Transact-SQL)

VARYING
指定支援做為輸出參數的結果集。 這個參數是預存程序所動態建構的,可能會有不同的內容。 只適用於 cursor 參數。 這個選項不適用於 CLR 程序。

預設值
這是參數的預設值。

OUT | OUTPUT
指出參數是一個傳回參數。

READONLY
指示無法在程序的主體內更新或修改參數。 如果參數類型是資料表值類型,就必須指定 READONLY。

RECOMPILE
表示 Database Engine 不會快取此程式的計畫,而且程式會在執行時間重新編譯。

ENCRYPTION
適用于:SQL Server ( SQL Server 2008 和更新版本) 和 Azure SQL Database。

表示 Database Engine 會將 ALTER PROCEDURE 語句的原始文字轉換成模糊格式。 混淆的輸出不會直接顯示在SQL Server的任何目錄檢視中。 對系統資料表或資料庫檔案沒有存取權的使用者,無法擷取混亂格式的文字。 不過,可透過 DAC 連接埠存取系統資料表或直接存取資料庫檔案的具特殊權限使用者,則可使用該文字。 另外,可將偵錯工具附加至伺服器處理序的使用者,可以在執行階段從記憶體擷取原始程序。 如需如何存取系統中繼資料的詳細資訊,請參閱中繼資料可見性組態

使用此選項建立的程式無法發佈為複寫SQL Server的一部分。

Common Language Runtime (CLR) 預存程序不能指定這個選項。

注意

在升級期間,Database Engine 會使用儲存在 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) 中的最佳做法、一般備註和限制一節。

外部名稱 assembly_nameclass_namemethod_name
適用于:SQL Server 2008 和更新版本。

指定要參考之 CLR 預存程式的.NET Framework元件方法。 class_name必須是有效的SQL Server識別碼,而且必須以類別的形式存在於元件中。 如果該類別的名稱符合命名空間規定,且該名稱利用句點 (.) 來分隔命名空間的各個部分,您就必須使用方括號 ([]) 或引號 ("") 來分隔類別名稱。 指定的方法必須是類別的靜態方法。

根據預設,SQL Server無法執行 CLR 程式碼。 您可以建立、修改和卸載參考 Common Language Runtime 模組的資料庫物件;不過,在啟用clr 啟用選項之前,您無法在 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 子句,並修改程序主體,使其只傳回提供指定之產品的供應商。 LEFTCASE 函數可自訂結果集的外觀。

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

另請參閱

CREATE PROCEDURE (Transact-SQL)
DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL)
EXECUTE AS (Transact-SQL)
EVENTDATA (Transact-SQL)
預存程序 (Database Engine)
sys.procedures (Transact-SQL)