SQL存储过程的详细用法，不信你看不懂（下）

--查询存储过程
IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_COUNT;
GO
CREATE procedure PROC_ORDER_COUNT
AS
   SELECT COUNT(OrderID) FROM Orders;
GO
--执行上述存储过程：
EXEC PROC_ORDER_COUNT;

--查询存储过程，根据城市查询总数
IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_COUNT;
GO
CREATE procedure PROC_ORDER_COUNT(@city nvarchar(50))
AS
   SELECT COUNT(OrderID) FROM Orders WHERE City=@city
GO
--执行上述存储过程：
EXEC PROC_ORDER_COUNT  N'GuangZhou';

--查询订单编号头两位是LJ的订单信息，含通配符
IF OBJECT_ID (N'PROC_ORDER_INFO', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_INFO;
GO
CREATE procedure PROC_ORDER_INFO
   @OrderID nvarchar(50)='LJ%' --默认值
AS
   SELECT OrderID,City,OrderDate,Price FROM Orders
   WHERE OrderID like @OrderID;
GO
--执行上述存储过程：
EXEC PROC_ORDER_INFO;
EXEC PROC_ORDER_INFO N'LJ%';
EXEC PROC_ORDER_INFO N'%LJ%';

--根据订单查询的信息，返回订单的城市及单价
IF OBJECT_ID (N'PROC_ORDER_INFO ', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_INFO ;
GO
CREATE procedure PROC_ORDER_INFO
   @orderid nvarchar(50),     --输入参数
   @city nvarchar(20) out,    --输出参数
   @price  float output        --输入输出参数
AS
   SELECT @city=City,@price=Price FROM Orders
   WHERE OrderID=@orderid AND Price=@price;
GO
--执行上述存储过程：
declare @orderid nvarchar(50),
       @city nvarchar(20),
       @price  int;
set @orderid= N'LJ0001';
set @price = 35.21;
exec PROC_ORDER_INFO @orderid,@city out, @price output;
select @city, @price;

--新增订单信息
IF OBJECT_ID (N'PROC_INSERT_ORDER', N'P') IS NOT NULL
   DROP procedure PROC_INSERT_ORDER;
GO
CREATE procedure PROC_INSERT_ORDER
   @orderid  nvarchar(50),
   @city nvarchar(20),
   @price float
AS
   INSERT INTO Orders(OrderID,City,Price)
   VALUES(@orderid,@city,@price)
GO
--执行
EXEC PROC_INSERT_ORDER N'LJ0001',N'GuangZhou',35.21;

--修改订单信息
IF OBJECT_ID (N'PROC_DELETE_ORDER', N'P') IS NOT NULL
   DROP procedure PROC_DELETE_ORDER;
GO
CREATE procedure PROC_DELETE_ORDER
   @orderid  nvarchar(50),
AS
   DELETE  FROM Orders WHERE OrderID=@orderid;
GO
--执行
EXEC PROC_DELETE_ORDER N'LJ0001';

--重复编译
IF OBJECT_ID (N'PROC_ORDER_WITH_RECOMPILE', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_WITH_RECOMPILE;
GO
CREATE procedure PROC_ORDER_WITH_RECOMPILE
with recompile --重复编译
AS
   SELECT * FROM Orders;
GO

--查询存储过程，进行加密，加密后不能查看和修改源脚本
IF OBJECT_ID (N'PROC_ORDER_WITH_ENCRYPTION', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_WITH_ENCRYPTION;
GO
CREATE procedure PROC_ORDER_WITH_ENCRYPTION
with encryption --加密
AS
   SELECT * FROM Orders;
GO
--执行上述存储过程：
EXEC PROC_ORDER_WITH_ENCRYPTION