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.