修改存储过程Modify a Stored Procedure

适用对象:yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

本主题介绍了如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中修改存储过程。This topic describes how to modify a stored procedure in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

开始之前Before You Begin

限制和局限Limitations and Restrictions

Transact-SQLTransact-SQL 存储过程修改为 CLR 存储过程,反之亦然。 stored procedures cannot be modified to be CLR stored procedures and vice versa.

如果原来的过程定义是使用 WITH ENCRYPTION 或 WITH RECOMPILE 创建的,那么只有在 ALTER PROCEDURE 语句中也包含这些选项时,这些选项才有效。If the previous procedure definition was created using WITH ENCRYPTION or WITH RECOMPILE, these options are enabled only if they are included in the ALTER PROCEDURE statement.

安全性Security

PermissionsPermissions

要求对过程具有 ALTER PROCEDURE 权限。Requires ALTER PROCEDURE permission on the procedure.

修改存储过程How to Modify a Stored Procedure

您可以使用以下项之一:You can use one of the following:

使用 SQL Server Management StudioUsing SQL Server Management Studio

在 Management Studio 中修改过程To modify a procedure in Management Studio

  1. 在对象资源管理器中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine and then expand that instance.

  2. 展开 “数据库”、过程所属的数据库以及 “可编程性”Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

  3. 展开“存储过程”,右键单击要修改的过程,再单击“修改”。Expand Stored Procedures, right-click the procedure to modify, and then click Modify.

  4. 修改存储过程的文本。Modify the text of the stored procedure.

  5. 若要测试语法,请在“查询” 菜单上单击“分析” 。To test the syntax, on the Query menu, click Parse.

  6. 若要将修改信息保存到过程定义中,请在 “查询” 菜单上单击 “执行”To save the modifications to the procedure definition, on the Query menu, click Execute.

  7. 若要将更新的过程定义另存为 Transact-SQLTransact-SQL 脚本,请在 “文件” 菜单上单击 “另存为”To save the updated procedure definition as a Transact-SQLTransact-SQL script, on the File menu, click Save As. 接受该文件名或将其替换为新的名称,再单击 “保存”Accept the file name or replace it with a new name, and then click Save.

重要

验证所有用户的输入。Validate all user input. 验证前请勿连接用户输入。Do not concatenate user input before you validate it. 绝对不要执行根据尚未验证的用户输入构造的命令。Never execute a command constructed from unvalidated user input.

使用 Transact-SQLUsing Transact-SQL

在查询编辑器中修改过程To modify a procedure in Query Editor

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine and then expand that instance.

  2. 展开 “数据库”,然后展开过程所属的数据库。Expand Databases, expand the database in which the procedure belongs. 或者,在工具栏上,从可用数据库列表中选择该数据库。Or, from the tool bar, select the database from the list of available databases. 对于此示例,选择 AdventureWorks2012AdventureWorks2012 数据库。For this example, select the AdventureWorks2012AdventureWorks2012 database.

  3. “文件” 菜单上,单击 “新建查询”On the File menu, click New Query.

  4. 复制以下示例并将其粘贴到查询编辑器中。Copy and paste the following example into the query editor. 此示例创建 uspVendorAllInfo 过程,该过程返回 Adventure Works CyclesAdventure Works Cycles 数据库中所有供应商的名称、所提供的产品、信用等级以及可用性。The example creates the uspVendorAllInfo procedure, which returns the names of all the vendors in the Adventure Works CyclesAdventure Works Cycles database, the products they supply, their credit ratings, and their availability.

    
    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  
    
    
  5. “文件” 菜单上,单击 “新建查询”On the File menu, click New Query.

  6. 复制以下示例并将其粘贴到查询编辑器中。Copy and paste the following example into the query editor. 该示例修改 uspVendorAllInfo 过程。The example modifies the uspVendorAllInfo procedure. 将删除 EXECUTE AS CALLER 子句并且将过程的主体修改为只返回那些提供指定产品的供应商。The EXECUTE AS CALLER clause is removed and the body of the procedure is modified to return only those vendors that supply the specified product. LEFTCASE 函数自定义结果集的外观。The LEFT and CASE functions customize the appearance of the result set.

    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  
    
    
  7. 若要将修改信息保存到过程定义中,请在 “查询” 菜单上单击 “执行”To save the modifications to the procedure definition, on the Query menu, click Execute.

  8. 若要将更新的过程定义另存为 Transact-SQLTransact-SQL 脚本,请在 “文件” 菜单上单击 “另存为”To save the updated procedure definition as a Transact-SQLTransact-SQL script, on the File menu, click Save As. 接受该文件名或将其替换为新的名称,再单击 “保存”Accept the file name or replace it with a new name, and then click Save.

  9. 若要运行修改的存储过程,请执行以下示例。To run the modified stored procedure, execute the following example.

    EXEC Purchasing.uspVendorAllInfo N'LL Crankarm';  
    GO  
    
    

另请参阅See Also

ALTER PROCEDURE (Transact-SQL)ALTER PROCEDURE (Transact-SQL)