S2 一本书 Day07课后题2-4

来源：互联网发布：java中观察者模式编辑：程序博客网时间：2024/05/03 11:04
/**2.存储过程实现分页 @page为第几页 @row为每页多少行*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_page') DROP PROCEDURE proc_pageGOCREATE PROCEDURE proc_page@page int,@row int =10  As    DECLARE @total AS int     SELECT @total=count(*) FROM BorrowIF @total<=(@page-1)*@row print('总条数为'+convert(varchar(5),@total)+'请确认参数')ELSESELECT 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_bookGOCREATE PROCEDURE proc_book@bname varchar(50) ASSELECT * FROM Book WHERE PubComp like @bnameGO--执行存储过程exec proc_book '北京%'/**4.存储过程统计某一时间段的借阅信息*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME='proc_borrow')DROP PROCEDURE proc_borrowGOCREATE PROCEDURE proc_borrow@beginDate datetime,  --注意参数不能直接用函数赋值@endDate datetimeAS  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 @endDateGO
0 0