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
执行后,效果