修改存储过程---Vincent-yuan

使用 Transact-SQL

1.在 “对象资源管理器” 中，连接到 数据库引擎 的实例，然后展开该实例。

2.展开 “数据库” ，然后展开过程所属的数据库。

3.在 “文件” 菜单上，单击 “新建查询” 。

4.复制以下示例并将其粘贴到查询编辑器中，先创建存储过程。该过程返回 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

之后，执行该存储过程，效果如下：

5.在 “文件” 菜单上，单击 “新建查询” 。

6.修改存储过程。

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.点击执行，即可以保持修改信息到存储过程。

8.要运行修改后的存储过程，可以执行下面所示

EXEC Purchasing.uspVendorAllInfo N'LL Crankarm'; GO

执行后，效果

---来自腾讯云社区的---Vincent-yuan