fine_sum.sql

图书馆系统建立

CREATE PROCEDURE Fine_Sum
(@book_id int)
as
DECLARE @out_day int,@may_day smalldatetime,@Fine_Sum int
set @may_day=datediff(day,(select lend_time from lend_book WHERE book_id=@book_id),(select return_maxday from return_maxday WHERE book_id=@book_id))
set @out_day=datediff(day,getdate(),@may_day)
IF @out_day>0
begin set @Fine_Sum=@out_day*(SELECT every_fine FROM limit_max)
RETURN @Fine_Sum
end
IF @out_day=0
begin
print ' 没有欠费！'
RETURN 0
END
IF @out_day<0
begin
print  '没有欠费！'
RETURN -100
END