April 26, 2014 at 3:50 am
Hi Guys,
I'm trying to implement a transaction in a stored procedure. I have to basically register a student in a class, and if the class has no empty seats, rollback back the transaction.
What I've done is working fine, it sets the values correctly, but I'm getting this weird error of ROLLBACK transaction has no corresponding BEGIN Transaction. And I've tried moving my COMMIT TRAN below and after the ROLLBACK TRAN and I'm still getting the same result.
Will be really thankful if someone can help with this. Been banging my head over this all day long.
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
IF @@TRANCOUNT > 0 COMMIT TRAN Tr1
SELECT @errorcode=@@ERROR
IF (@ErrorCode<>0) BEGIN GOTO OTHERPROBLEM END
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
IF @@TRANCOUNT > 0 COMMIT TRAN Tr2
SELECT @errorcode=@@ERROR
IF (@ErrorCode<>0) BEGIN GOTO OTHERPROBLEM2 END
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
INSERT INTO dbo.CourseEnrollment (dbo.CourseEnrollment.StudentId,dbo.CourseEnrollment.CourseId)
VALUES(@StudentID,@CourseID);
UPDATE dbo.Courses
SET OpenSeats = OpenSeats-1
WHERE dbo.Courses.CourseId = @CourseID
SELECT @CurrentSeats=OpenSeats
FROM dbo.Courses
WHERE dbo.Courses.CourseId = @CourseID
IF @@TRANCOUNT > 0COMMIT TRAN Tr3
IF (@CurrentSeats<0) BEGIN GOTO PROBLEM END
END
END
PROBLEM:
BEGIN
PRINT 'Seats Full!'
ROLLBACK TRAN Tr3
END
OTHERPROBLEM:
BEGIN
PRINT 'Unable to set status'
ROLLBACK TRAN Tr1
END
OTHERPROBLEM2:
BEGIN
PRINT 'Unable to set status'
ROLLBACK TRAN Tr2
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;
April 26, 2014 at 6:05 am
April 26, 2014 at 6:51 am
This link talks about nested transactions. But I don't have any nested transactions. They are normal.
April 26, 2014 at 7:27 am
rohanbajaj90 (4/26/2014)
This link talks about nested transactions. But I don't have any nested transactions. They are normal.
Sorry, seams like I jumped the gun here:-D
I formatted the code and added few comments, there will be conditions where the transactions have already been rolled back or committed before hitting commit/rollback
😎
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
IF @@TRANCOUNT > 0 --
COMMIT TRAN Tr1 -- First commit of Tr1
--
SELECT @ErrorCode=@@ERROR --
--
IF (@ErrorCode<>0) --
BEGIN --
GOTO OTHERPROBLEM -- No check for open transactions
END --
--
COMMIT TRAN Tr1 -- Second commit of 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
IF @@TRANCOUNT > 0
COMMIT TRAN Tr2 -- No transactions after codes hits this point
SELECT @ErrorCode=@@ERROR
IF (@ErrorCode<>0)
BEGIN
GOTO OTHERPROBLEM2 -- No check for open transactions
END
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
INSERT INTO dbo.CourseEnrollment (dbo.CourseEnrollment.StudentId,dbo.CourseEnrollment.CourseId)
VALUES (@StudentID,@CourseID);
UPDATE dbo.Courses
SET OpenSeats = OpenSeats-1
WHERE dbo.Courses.CourseId = @CourseID
SELECT @CurrentSeats=OpenSeats
FROM dbo.Courses
WHERE dbo.Courses.CourseId = @CourseID
IF @@TRANCOUNT > 0
COMMIT TRAN Tr3 -- No transactions after codes hits this point
IF (@CurrentSeats<0)
BEGIN
GOTO PROBLEM -- No check for open transactions
END
END
END -- Mismatched END ???
PROBLEM:
BEGIN
PRINT 'Seats Full!'
ROLLBACK TRAN Tr3 -- No check for open transactions
END
OTHERPROBLEM:
BEGIN
PRINT 'Unable to set status'
ROLLBACK TRAN Tr1 -- No check for open transactions
END
OTHERPROBLEM2:
BEGIN
PRINT 'Unable to set status'
ROLLBACK TRAN Tr2 -- No check for open transactions
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; -- Mismatched END ???
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply