TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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.
Reply
Answers (
4
)
SQL server Management studio express
Sending parameter values to a report in reporting services