--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 } [;]
![](http://i.msdn.microsoft.com/Global/Images/clear.gif)
不能将 Transact-SQL 存储过程修改为 CLR 存储过程,反之亦然。
ALTER PROCEDURE 不会更改权限,也不影响相关的存储过程或触发器。但是,当修改存储过程时,QUOTED_IDENTIFIER 和 ANSI_NULLS 的当前会话设置包含在该存储过程中。如果设置不同于最初创建存储过程时有效的设置,则存储过程的行为可能会更改。
如果原来的过程定义是使用 WITH ENCRYPTION 或 WITH RECOMPILE 创建的,那么只有在 ALTER PROCEDURE 中也包含这些选项时,这些选项才有效。
有关存储过程的详细信息,请参阅 CREATE PROCEDURE (Transact-SQL)。
![](http://i.msdn.microsoft.com/Global/Images/clear.gif)
以下示例将创建 uspVendorAllInfo 存储过程。此过程返回提供 Adventure Works Cycles 的所有供应商的名称、所提供的产品、信用等级以及可用性。创建过程之后,便可修改过程以返回不同的结果集。
USE AdventureWorks2008R2; 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 AdventureWorks2008R2; 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 EXEC Purchasing.uspVendorAllInfo N'LL Crankarm'; GO
下面是结果集:
Vendor Product name Rating Availability
-------------------- ------------- ------- ------------
Proseware, Inc. LL Crankarm Average No
Vision Cycles, Inc. LL Crankarm Superior Yes
(2 row(s) affected)