-- ERROR HANDLING EXAMPLE /* REMOVEMEMBER Deletes a library member by deleting the row in the member table, and associated row in either the adult or juvenile tables. Reservations for the member are also deleted. Error checking ensures that members with books currently on loan cannot be deleted. Adult members responsible for juvenile members also cannot be deleted. */ /* The following user-defined messages support the removemember stored procedure. */ EXEC sp_addmessage 50010, 10, 'Member number not found.' EXEC sp_addmessage 50011, 10, 'Member cannot be deleted.' EXEC sp_addmessage 50012, 10, 'Member is responsible for juvenile member.' EXEC sp_addmessage 50013, 10, 'Member currently has books on loan.' CREATE PROCEDURE dbo.removemember @member_no member_no = NULL AS DECLARE @RetVal INT SET @RetVal = -1 -- Default return value: Indicates error. IF @member_no IS NULL BEGIN PRINT 'You must supply a member number' RETURN @RetVal END IF NOT EXISTS (SELECT * FROM member WHERE member_no = @member_no) BEGIN RAISERROR (50010, 10, 1) -- Member not found. RETURN @RetVal END IF EXISTS (SELECT * FROM juvenile WHERE adult_member_no = @member_no) BEGIN RAISERROR (50011, 10, 1) -- Member cannot be deleted. RAISERROR (50012, 10, 1) -- Member responsible for juvenile. SELECT j.member_no, juvenile_name = RTRIM(lastname) + ' ', firstname, birth_date FROM juvenile j JOIN member m ON j.member_no = m.member_no WHERE @adult_member_no = @member_no PRINT 'Remove the juvenile member(s) first or specify a new adult member' RETURN @RetVal END IF EXISTS (SELECT * FROM loan WHERE member_no = @member_no) BEGIN RAISERROR (50011, 10, 1) -- Member cannot be deleted. RAISERROR (50013, 10, 1) -- Member has books on loan. SELECT due_date, isbn, copy_no, title FROM OnLoanView WHERE member_no = @member_no RETURN @RetVal END IF EXISTS (SELECT * FROM loanhist WHERE member_no = @member_no) BEGIN SELECT 'Member owes $' + COALESCE( CAST( (SUM(COALESCE(fine_assessed, 0.00) - COALESCE(fine_paid, 0.00)) - SUM(COALESCE(fine_waived, 0.00))) AS CHAR(6)), 0.00) + ' fines' FROM loanhist WHERE member_no = @member_no END SET @RetVal = 0 -- Return value indicating success. BEGIN TRANSACTION IF EXISTS (SELECT * FROM reservation WHERE member_no = @member_no) BEGIN PRINT 'Deleting loan reservation information' DELETE reservation WHERE member_no = @member_no END IF @@ERROR <> 0 GOTO Rollback IF EXISTS (SELECT * FROM juvenile WHERE member_no = @member_no) BEGIN PRINT 'Deleting juvenile member information' DELETE juvenile WHERE member_no = @member_no END ELSE IF EXISTS (SELECT * FROM adult WHERE member_no = @member_no) BEGIN PRINT 'Deleting adult member information' DELETE adult WHERE member_no = @member_no END IF @@ERROR <> 0 GOTO Rollback DELETE member WHERE member_no = @member_no IF @@ERROR <> 0 GOTO Rollback SELECT 'Member ' + CAST(@member_no AS CHAR(6)) + ' has been removed from the library database.' COMMIT TRANSACTION Finish: RETURN @RetVal Rollback: IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION SET @RetVal = -1 GOTO Finish