Ed

Ed

  • NA
  • 8
  • 0

Criticize me, Please...

Feb 12 2009 4:56 PM
I would appreciate a critique on the following stored procedure, if anyone is willing:
==========
USE EDLibrary
GO
/****** Object:  StoredProcedure Items.CheckIn    Script Date: 01/30/2009 11:03:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Items.CheckIn') AND type in (N'P', N'PC'))
  DROP PROCEDURE Items.CheckIn
GO

/****** Object:  StoredProcedure Items.CheckIn    Script Date: 01/30/2009 11:04:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC Items.CheckIn (
  @ISBN     INT,
  @CopyNo   INT,
  @Result   INT OUTPUT
)
AS
SET NOCOUNT ON
--Do we have ISBN?
IF (@ISBN IS NULL)
  RAISERROR('An ISBN must be supplied.', 16, 1)

--Do we have Copy No?
IF (@ISBN IS NULL)
  RAISERROR('A Copy Number must be supplied.', 16, 2)

BEGIN TRY
  BEGIN TRANSACTION
--  Record this loan for posterity.
    INSERT INTO LoanHist
    SELECT ISBN, Copy_No, Out_Date, Title_No, Member_No, Due_Date, GETDATE(), NULL, NULL, NULL, NULL
    FROM Loan
    WHERE ISBN    = @ISBN
    AND   Copy_No = @CopyNo

--  Check to see if the update was successful.
    SET @Result = @@ERROR
    IF @Result <> 0
      RAISERROR('The LoanHist table was not updated.', 16, @Result)

--  Remove current loan from the record.
    DELETE FROM Loan
    WHERE ISBN    = @ISBN
    AND   Copy_No = @CopyNo

--  Check to see if the update was successful.
    SET @Result = @@ERROR
    IF @Result <> 0
      RAISERROR('The Loan table was not updated.', 16, @Result)

--  Indicate that the copy is no longer on loan.
    UPDATE Copy
    SET On_Loan = 'N'
    WHERE ISBN    = @ISBN
    AND   Copy_No = @CopyNo

--  Check to see if the update was successful.
    SET @Result = @@ERROR
    IF @Result <> 0
      RAISERROR('The Copy table was not updated.', 16, @Result)

    SET @Result = 0
  COMMIT TRANSACTION
END TRY

--Report on any errors should they occur.
BEGIN CATCH
  SET @Result = error_state()
  SELECT @Result 'Error State', error_message() 'Error Message'
  ROLLBACK TRANSACTION
END CATCH
==========

Thanks,
Ed.


Answers (4)