*2.存储过程实现分页 @page为第几页 @row为每页多少行
*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_page')
DROP PROCEDURE proc_page
GO
CREATE PROCEDURE proc_page
@page int,
@row int =10
As
DECLARE @total AS int
SELECT @total=count(*) FROM Borrow
IF @total<=(@page-1)*@row
print('总条数为'+convert(varchar(5),@total)+'请确认参数')
ELSE
SELECT TOP (@row) * FROM Borrow WHERE rid NOT IN(SELECT TOP ((@page-1)*@row) rid FROM Borrow)
GO
--执行语句
exec proc_page 1
/*
*3.存储过程统计已'北京'冠名的出版社的图书信息
*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_book')
DROP PROCEDURE proc_book
GO
CREATE PROCEDURE proc_book
@bname varchar(50)
AS
SELECT * FROM Book WHERE PubComp like @bname
GO
--执行存储过程
exec proc_book '北京%'
/*
*4.存储过程统计某一时间段的借阅信息
*/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_borrow')
DROP PROCEDURE proc_borrow
GO
CREATE PROCEDURE proc_borrow
@beginDate datetime, --注意参数不能直接用函数赋值
@endDate datetime
AS
IF @beginDate IS NULL
SET @beginDate=DateAdd(MM,-1,getDate())
IF @endDate IS NULL
SET @endDate=getDate()
SELECT * FROM Borrow WHERE LendDate BETWEEN @beginDate AND @endDate
GO
优化MySchool 第七章课后
最新推荐文章于 2017-02-24 17:51:58 发布