SQL Server存储过程复习（一）

--存储过程学习篇 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.Orders 11 GO 12 13 --2.简单存储过程，带参数 14 IF OBJECT_ID(‘Orders_GetAllByName‘,‘P‘) IS NOT NULL 15 DROP PROCEDURE Orders_GetAllByName; 16 GO 17 CREATE PROCEDURE Orders_GetAllByName 18 @ShipName NVARCHAR(40) 19 AS 20 SELECT * FROM dbo.Orders 21 WHERE ShipName=@ShipName 22 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 NULL 31 DROP PROCEDURE Employees_GetAllByName; 32 GO 33 CREATE PROCEDURE Employees_GetAllByName 34 @FirstName NVARCHAR(10)=N‘%‘, 35 @LastName NVARCHAR(20)=N‘D%‘ 36 AS 37 SELECT * FROM dbo.Employees 38 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 NULL 47 DROP PROCEDURE GetManyResultsCount; 48 GO 49 CREATE PROCEDURE GetManyResultsCount 50 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 GetmanyProducts 63 @ProductName NVARCHAR(40), 64 @MaxPrice MONEY, 65 @ComparePrice MONEY OUTPUT, 66 @UnitPrice MONEY OUTPUT 67 AS 68 SELECT p.ProductName,p.UnitPrice FROM dbo.Products AS P 69 INNER JOIN dbo.Categories AS C ON C.CategoryID = P.CategoryID 70 WHERE p.ProductName LIKE @ProductName AND p.UnitPrice<@MaxPrice 71 72 --设置输出参数 73 SET @UnitPrice=( 74 SELECT MAX(P.UnitPrice) FROM dbo.Products AS P 75 JOIN dbo.Categories AS C ON C.CategoryID = P.CategoryID 76 WHERE p.ProductName LIKE @ProductName AND p.UnitPrice<@MaxPrice 77 ) 78 SET @ComparePrice=@MaxPrice;

 

SQL Server存储过程复习（一）

标签：