October 5, 2011 at 12:11 am
I'm using two table for inserting Attendance of all students.
tblAttendanceClasses - Save the record of class.(one record with todays date).
tblAttendanceStudents- Save log of all students attendance.
The whole store procedure is working fine.
But I want to delte the Inserted records from both table tblAttendanceClasses
and tblAttendanceStudents if any error occures while Inserting the student record.
Suppose there are 10 records to Insert.
First 5 records are Inserted and if there is any error in 6th record then the
records inserted in tblAttendanceClasses and tblAttendanceStudents
should be deleted.
The following code is just for reference.
I'm confused where to add Delete Statement.
Check the Image for table Structure.
CREATE PROCEDURE up_dt_AttendanceStudentClass_Upload
(
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@DOB DATETIME,
@status VARCHAR(20),
@Reason VARCHAR(200),
@TotalPresent INT,
@TotalAbsent INT,
@AttendanceDate DATETIME,
@ErrorMessage VARCHAR(1000) OUTPUT
)
AS
.
.
.
SELECT @ContactId = Select cntcContactId from .......
IF (@ContactId = 0)
GOTO ContactError
SELECT @AudienceBatchId = SELECT abchAudienceBatchId from ......
IF (@AudienceBatchId = 0)
GOTO ClassBatchError
Select @AttendanceClassCount = Select COUNT(*) from .......
Select @AttyId = attyAttendanceStatusTypeId from .......
IF (@AttyId = 0)
GOTO StatusError
IF (@AttendanceClassCount = 0)
--Insert into tblAttendanceClasses
EXEC @terror = up_tblAttendanceClasses_insert ......
SELECT @SysError = @@error
IF @SysError <> 0 OR @terror <> 0 --Log error
BEGIN
SELECT @errorcode = @SysError
SELECT @ErrorMessage = 'Error in Procedure: ClassInsert'
ROLLBACK TRANSACTION AttendanceStudentClassUpload
EXEC @terror = up_tblAttendanceStudents_delete
GOTO LogError
END
--Insert into tblAttendanceStudents First Record of Student
EXEC @terror = up_tblAttendanceStudents_insert ........
SELECT @SysError = @@error
IF @SysError <> 0 OR @terror <> 0 --Log error
BEGIN
SELECT @errorcode = @SysError
SELECT @ErrorMessage = 'Error in Procedure: StudentInsert'
ROLLBACK TRANSACTION AttendanceStudentClassUpload
GOTO LogError
END
ELSE IF (@AttendanceClassCount > 0)
SELECT @AttendanceClassId = Select atclAttendanceClassId .....
SELECT @AttendanceStudentCount = SELECT Count(*) FROM .....
IF (@AttendanceStudentCount = 0)
--Insert Into tblAttendanceStudents Second Record of Student and Onwards
EXEC @terror = up_tblAttendanceStudents_insert ......
SELECT @SysError = @@error
IF @SysError <> 0 OR @terror <> 0 --Log error
BEGIN
SELECT @errorcode = @SysError
SELECT @ErrorMessage = 'Error in Procedure: StudentInsert'
ROLLBACK TRANSACTION AttendanceStudentClassUpload
GOTO LogError
END
IF (@BeginTranFlag = 1)
COMMIT TRANSACTION AttendanceStudentClassUpload
RETURN (0)
ContactError:
SELECT @ErrorMessage ='Unable to find student detail "'
RETURN(-99)
ClassBatchError:
SELECT @ErrorMessage = 'Unable to find student class"'
RETURN(-99)
StatusError:
SELECT @ErrorMessage = 'Unable to find status of student "'
RETURN(-99)
LogError:
IF @errorcode <> 0
SELECT @TodayTime = getdate()
SELECT @ErrorMessage = 'Error , ' + IsNull(@ErrorMessage,'')
RETURN(-99)
End
October 5, 2011 at 8:42 am
Have you tried using a TRY..CATCH block wrapped around the INSERT statement, this will allow you to do something after trapping/planning for the potential error, Ex:
BEGIN TRY
INSERT INTO ...
SELECT @SysError = @@error
END TRY
BEGIN CATCH
'Add logic here to DELETE your record, roll back your transaction(s), etc.
END CATCH
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 7, 2011 at 1:25 am
Ok but for Deleting the record it requires atclAttendanceClassId and atstAtclId.
For Example :
I've to write delete query inside catch block :
"Delete From tblAttendanceStudents Where atstAtclId = 34"
But if error occurs then the execution will be from catch block directly.
Here I'm finding Contact Id first then other Ids.
If it give error in ContactId itself then how will I get the atclAttendanceClassId and atstAtclId ?
Thanks
October 7, 2011 at 1:40 am
Don't think about manually deleting them. Do all the operations in a transaction and let SQL manage the undo if there's an error.
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO tblAttendanceClasses ...
INSERT INTO tblAttendanceStudents ...
COMMIT TRANSACTION
END TRY
BEGIN CATCH
BEGIN CATCH
ROLLBACK TRANSACTION -- undoes everything back to the Begin Tran
DECLARE @errorMsg VARCHAR(50) = ERROR_MESSAGE(),
@errorLine INT = ERROR_LINE(),
@errorSeverity INT = ERROR_SEVERITY(),
@errorState INT = ERROR_STATE();
RAISERROR ('Insert of student attandance failed with error "%s" on line %u', @errorSeverity, @errorState, @errorMsg, @errorLine);
END CATCH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply