BDG Scenario 1

fm_admin_checkin.sql

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

CREATE Procedure fm_admin_checkin
    (
        @barcode varchar(15),
        @location varchar(64)
    )
As
    set nocount on
    
    declare @bibNo integer
   declare @status varchar(7)
    
    select @bibNo = bib#, @status = item_status from item
           where barCode = @barCode
           
    if @status<>'in'
    begin
       update item set location=@location, item_status='in', borrower#=null,
              due_date=null, last_overdue_notice=null
              where barcode=@barcode
      return @bibNo
   end
   
   return 0
    

Go