Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE PROCEDURE dbo.EnrollStudent ( @CourseID AS INTEGER,
- @StudentID AS VARCHAR(20) ) AS
- BEGIN
- DECLARE @StatusID INTEGER
- DECLARE @Status VARCHAR(50)
- DECLARE @CurrentSeats INTEGER
- DECLARE @ErrorCode INTEGER
- SET @StatusID=0
- IF EXISTS (SELECT 1
- FROM dbo.CourseEnrollment
- WHERE dbo.CourseEnrollment.CourseId=@CourseID AND dbo.CourseEnrollment.StudentId=@StudentID )
- BEGIN
- BEGIN TRAN Tr1
- SET @StatusID = 1
- SELECT @ErrorCode=@@ERROR
- IF (@ErrorCode<>0) GOTO OTHERPROBLEM
- ELSE
- COMMIT TRAN Tr1
- END
- IF EXISTS ( SELECT 1
- FROM dbo.CourseEnrollment
- FULL OUTER JOIN dbo.Courses
- ON dbo.Courses.CourseId=@CourseID
- WHERE dbo.CourseEnrollment.StudentId<>@StudentID AND dbo.Courses.Faculty IS NULL )
- BEGIN
- BEGIN TRAN Tr2
- SET @StatusID=2
- SELECT @ErrorCode=@@ERROR
- IF (@ErrorCode<>0) GOTO OTHERPROBLEM2
- ELSE
- COMMIT TRAN Tr2
- END
- IF @StatusID=0
- BEGIN
- IF EXISTS ( SELECT 1
- FROM dbo.Courses
- WHERE dbo.Courses.CourseId=@CourseID AND dbo.Courses.Faculty IS NOT NULL )
- BEGIN
- BEGIN TRAN Tr3
- SET @StatusID=3
- BEGIN TRAN InsertingValues
- INSERT INTO dbo.CourseEnrollment (dbo.CourseEnrollment.StudentId,dbo.CourseEnrollment.CourseId)
- VALUES (@StudentID,@CourseID);
- SELECT @ErrorCode=@@ERROR
- IF (@ErrorCode<>0) GOTO InsertProblem
- ELSE
- COMMIT TRAN InsertingValues
- BEGIN TRAN UpdateCourses
- UPDATE dbo.Courses
- SET OpenSeats = OpenSeats-1
- WHERE dbo.Courses.CourseId = @CourseID
- SELECT @ErrorCode=@@ERROR
- IF (@ErrorCode<>0) GOTO UpdateProblem
- ELSE
- COMMIT TRAN UpdateCourses
- SELECT @CurrentSeats=OpenSeats
- FROM dbo.Courses
- WHERE dbo.Courses.CourseId = @CourseID
- IF (@CurrentSeats<0) GOTO PROBLEM
- ELSE
- COMMIT TRAN Tr3
- END
- END
- OTHERPROBLEM:
- BEGIN
- PRINT 'Unable to set status'
- ROLLBACK TRAN
- END
- OTHERPROBLEM2:
- BEGIN
- PRINT 'Unable to set status'
- ROLLBACK TRAN
- END
- UpdateProblem:
- BEGIN
- PRINT 'Not able to update values'
- ROLLBACK TRAN InsertingValues
- END
- InsertProblem:
- BEGIN
- PRINT 'Not able to insert'
- ROLLBACK TRAN InsertingValues
- END
- PROBLEM:
- BEGIN
- PRINT 'Seats Full!'
- ROLLBACK TRAN
- END
- IF @StatusID = 1
- BEGIN
- SET @Status = 'The Student is already enrolled'
- END;
- ELSE IF @StatusID = 2
- BEGIN
- SET @Status = 'Cannot enroll until faculty is selected'
- END
- ELSE IF @StatusID = 3
- BEGIN
- SET @Status = 'Student Enrolled'
- END
- SELECT @Status
- END;
- (1 row(s) affected)
- (1 row(s) affected)
- Unable to set status
- Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 101
- The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
- Unable to set status
- Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 108
- The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
- Not able to update values
- Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 115
- The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
- Not able to insert
- Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 123
- The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
- Seats Full!
- Msg 3903, Level 16, State 1, Procedure EnrollStudent, Line 131
- The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
- (1 row(s) affected)
- BEGIN TRANSACTION;
- BEGIN TRY
- --execute all your stored proc code here and then commit
- COMMIT;
- END TRY
- BEGIN CATCH
- --if an exception occurs execute your rollback, also test that you have had some successful transactions
- IF @@TRANCOUNT > 0 ROLLBACK;
- END CATCH
- BEGIN TRAN Tr1
- -- your code
- ROLLBACK TRAN Tr1
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement