fm_admin_renew.sql

If Exists (Select * From sysobjects Where name = N'fm_admin_renew' And user_name(uid) = N'dbo')
    Drop Procedure dbo.fm_admin_renew
Go

CREATE Procedure fm_admin_renew
    (
        @barcode varchar(15),
        @dueDate char(10)
    )
As
    set nocount on
    
    declare @itemNo int
    declare @bibNo int
    declare @n_renewals int
    declare @lngthRequest int
    declare @lngthItem int
    
    select @bibNo=bib#, @itemNo=item#, @n_renewals=n_renewals from item where barcode=@barcode
    
    -- First check with request table to see if there are present requests for this title
   exec @lngthRequest = fm_queue_length @bibNo
   
    -- If requests exist, this renewal can be satisfied only if enough items are available
   if @lngthRequest > 0
      select @lngthItem = count(*) from item where bib#=@bibNo and item_status in 
                         (select item_status from item_status where available_for_request=1)
   else
      select @lngthItem = 1  -- dummy value for logic below
      
   if @lngthItem - @lngthRequest > 0
      begin         
          -- NOTE: Should probably limit the number of times an item can be renewed...
         select @n_renewals = @n_renewals + 1
          update item set due_date=@dueDate, n_renewals=@n_renewals where item#=@itemNo
         return @n_renewals
       end
   else       
        return 0               -- Indicate that no renewal was made  



Go