SQL Server存储过程复习（一）

--存储过程学习篇

--1.简单存储过程不带参数的学习
IF OBJECT_ID('Orders_GetAllOrders','P') IS  NOT NULL 
DROP PROCEDURE Orders_GetAllOrders;
GO
CREATE PROC  Orders_GetAllOrders
AS
SET NOCOUNT ON;
SELECT * FROM dbo.Orders
GO 

--2.简单存储过程，带参数
IF OBJECT_ID('Orders_GetAllByName','P') IS NOT NULL
DROP PROCEDURE Orders_GetAllByName;
GO 
CREATE PROCEDURE Orders_GetAllByName
@ShipName NVARCHAR(40)
AS 
SELECT * FROM dbo.Orders
WHERE ShipName=@ShipName
GO 

--执行带参数的存储过程
EXEC Orders_GetAllByName @ShipName=N'Hanari Carnes'


--3.使用带有通配符参数的简单存储过程

IF OBJECT_ID('Employees_GetAllByName','P') IS NOT NULL
DROP PROCEDURE Employees_GetAllByName;
GO 
CREATE PROCEDURE Employees_GetAllByName
@FirstName NVARCHAR(10)=N'%',
@LastName  NVARCHAR(20)=N'D%'
AS 
SELECT * FROM dbo.Employees
WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;

--执行存储过程
EXECUTE dbo.Employees_GetAllByName @FirstName = N'Nancy', -- nvarchar(10)
    @LastName = N'Davolio' -- nvarchar(20)


--4.返回多个结果集
IF OBJECT_ID('GetManyResultsCount','P') IS NOT NULL
DROP PROCEDURE GetManyResultsCount;
GO 
CREATE PROCEDURE GetManyResultsCount
AS 
SELECT COUNT(*) FROM dbo.Orders;
SELECT COUNT(*) FROM dbo.Employees;
GO  

--执行存储过程
EXEC GetManyResultsCount;

--使用 OUTPUT 参数的存储过程
IF OBJECT_ID('GetmanyProducts','P') IS NOT NULL 
DROP PROCEDURE GetmanyProducts;
GO 
CREATE PROCEDURE GetmanyProducts
@ProductName NVARCHAR(40),
@MaxPrice MONEY,
@ComparePrice MONEY OUTPUT,
@UnitPrice MONEY OUTPUT
AS 
SELECT p.ProductName,p.UnitPrice FROM dbo.Products AS P
INNER JOIN dbo.Categories AS C ON C.CategoryID = P.CategoryID
WHERE p.ProductName LIKE @ProductName AND p.UnitPrice<@MaxPrice

--设置输出参数
SET @UnitPrice=(
SELECT  MAX(P.UnitPrice) FROM dbo.Products AS P
JOIN dbo.Categories AS C ON C.CategoryID = P.CategoryID
WHERE p.ProductName LIKE @ProductName AND p.UnitPrice<@MaxPrice
)
SET @ComparePrice=@MaxPrice;