SQL Commit and Rollback Example

ALTER PROCEDURE [dbo].[sp_sessional_marks_insert]
(
@StudentId int,
@TermId varchar(50),
@CourseId varchar(50),
@LabPerformance decimal (18,2),
@Attendance decimal (18,2),
@Quiz decimal (18,2)
)
AS
BEGIN
DECLARE @intErrorCode int
begin transaction
UPDATE T_Term_Student_Result_Lab_Details
SET
LabPerformance = @LabPerformance,
Attendance = @Attendance,
QuizViva = @Quiz
WHERE StudentId = @StudentId AND TermId = @TermId AND CourseId = @CourseId
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode 0) GOTO PROBLEMERROR
UPDATE T_Term_Script_Submission_Status_Compact
SET
AttendanceClassTest=GETDATE(),
SessionalSubmitDate=GETDATE()
WHERE TermId=@TermId AND CourseId=@CourseId
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode 0) GOTO PROBLEMERROR
commit transaction
PROBLEMERROR:
IF (@intErrorCode 0)
BEGIN
ROLLBACK TRAN
END
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s