1 --存儲過程學習篇 2  3 --1.簡單存儲過程不帶參數的學習 4 IF OBJECT_ID('Orders_GetAllOrders','P') IS  NOT NULL  5 DROP PROCEDURE Orders_GetAllOrders; 6 GO 7 CREATE PROC  Orders_GetAllOrders 8 AS 9 SET NOCOUNT ON;10 SELECT * FROM dbo.Orders11 GO 12 13 --2.簡單存儲過程，帶參數14 IF OBJECT_ID('Orders_GetAllByName','P') IS NOT NULL15 DROP PROCEDURE Orders_GetAllByName;16 GO 17 CREATE PROCEDURE Orders_GetAllByName18 @ShipName NVARCHAR(40)19 AS 20 SELECT * FROM dbo.Orders21 WHERE ShipName=@ShipName22 GO 23 24 --執行帶參數的存儲過程25 EXEC Orders_GetAllByName @ShipName=N'Hanari Carnes'26 27 28 --3.使用帶有通配符參數的簡單存儲過程29 30 IF OBJECT_ID('Employees_GetAllByName','P') IS NOT NULL31 DROP PROCEDURE Employees_GetAllByName;32 GO 33 CREATE PROCEDURE Employees_GetAllByName34 @FirstName NVARCHAR(10)=N'%',35 @LastName  NVARCHAR(20)=N'D%'36 AS 37 SELECT * FROM dbo.Employees38 WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;39 40 --執行存儲過程41 EXECUTE dbo.Employees_GetAllByName @FirstName = N'Nancy', -- nvarchar(10)42     @LastName = N'Davolio' -- nvarchar(20)43 44 45 --4.返回多個結果集46 IF OBJECT_ID('GetManyResultsCount','P') IS NOT NULL47 DROP PROCEDURE GetManyResultsCount;48 GO 49 CREATE PROCEDURE GetManyResultsCount50 AS 51 SELECT COUNT(*) FROM dbo.Orders;52 SELECT COUNT(*) FROM dbo.Employees;53 GO  54 55 --執行存儲過程56 EXEC GetManyResultsCount;57 58 --使用 OUTPUT 參數的存儲過程59 IF OBJECT_ID('GetmanyProducts','P') IS NOT NULL 60 DROP PROCEDURE GetmanyProducts;61 GO 62 CREATE PROCEDURE GetmanyProducts63 @ProductName NVARCHAR(40),64 @MaxPrice MONEY,65 @ComparePrice MONEY OUTPUT,66 @UnitPrice MONEY OUTPUT67 AS 68 SELECT p.ProductName,p.UnitPrice FROM dbo.Products AS P69 INNER JOIN dbo.Categories AS C ON C.CategoryID = P.CategoryID70 WHERE p.ProductName LIKE @ProductName AND p.UnitPrice<@MaxPrice71 72 --設置輸出參數73 SET @UnitPrice=(74 SELECT  MAX(P.UnitPrice) FROM dbo.Products AS P75 JOIN dbo.Categories AS C ON C.CategoryID = P.CategoryID76 WHERE p.ProductName LIKE @ProductName AND p.UnitPrice<@MaxPrice77 )78 SET @ComparePrice=@MaxPrice;