SQL Server存储过程复习（一）

 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;