一个存储过程（练习）

if exists(select name from sysobjects where name='p_borrow' and type='p')
drop proc p_borrow
go
create proc p_borrow
@p_cardid varchar(10),
@p_bookid varchar(10),
@p_borrowdate varchar(10),
@p_returndate varchar(10)
as
  begin tran
  if(@p_bookid not in(select bookid from bookstore))
    begin
      print '对不起，图书馆中不存在这本书！'
      rollback tran
    end
  if((select state from bookstore where bookid=@p_bookid)='出库')
    begin
      print '对不起，这本书已经被借出！'
      rollback tran
    end
  else
    begin
      update bookstore
      set state='出库'
      where bookid=@p_bookid
      insert
      into borrowlog(cardid,bookid,borrowdate,returndate)
      values(@p_cardid,@p_bookid,@p_borrowdate,@p_returndate)
      if @@error<>0
         begin
           print '对不起，没有此人的卡号，故不能借书！'
           rollback tran
         end
      else
        begin
           declare @day int
           select @day=datediff(dd,@p_borrowdate,@p_returndate)
           --select @day as 借阅天数
           print '借阅天数'
           print '------------'
           print @day
           if(@day>60)
             begin
                print '------------'
                print '对不起，该用户借出超期，请对其进行相应罚款后再进行借书！'
                rollback tran
             end
           else
             begin
               print '你好，这本书已成功借出！'
               commit tran
             end
        end
  end