Transaction problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transaction problem

Author

Topic

matta0990
Starting Member

44 Posts

Posted - 2011-05-22 : 13:42:07

Im not sure why this transaction is not working, i no there is a problem and im new to the SQL thing. Any help? I need it to limit the booking room to 200 so once it hits 200 it rollback the transaction.

CREATE PROCEDURE stp_PeopleBook
AS
DECLARE @BookingRoom INT
SET @BookingRoom = 0

BEGIN TRANSACTION
IF EXISTS (SELECT BookingRoom FROM Booking WHERE RoomID = 1)

IF (@BookingRoom <200)
BEGIN
UPDATE Booking
SET BookingRoom = BookingRoom +1
WHERE CustomerID = 1
END
IF (@BookingRoom >200)
BEGIN
ROLLBACK TRANSACTION
PRINT 'error'
END
ELSE
BEGIN TRANSACTION
COMMIT TRANSACTION
PRINT 'Transaction Commited'
END

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-22 : 14:02:17

quote:

Im not sure why this transaction is not working, i no there is a problem and im new to the SQL thing. Any help? I need it to limit the booking room to 200 so once it hits 200 it rollback the transaction.

CREATE PROCEDURE stp_PeopleBook
AS
DECLARE @BookingRoom INT
SET @BookingRoom = 0

BEGIN TRANSACTION
IF EXISTS (SELECT BookingRoom FROM Booking WHERE RoomID = 1)

IF (@BookingRoom <200)
BEGIN
UPDATE Booking
SET BookingRoom = BookingRoom +1
WHERE CustomerID = 1
END
IF (@BookingRoom >200)
BEGIN
ROLLBACK TRANSACTION
PRINT 'error'
END
ELSE
BEGIN TRANSACTION
COMMIT TRANSACTION
PRINT 'Transaction Commited'
END

Well, you're setting @BookingRoom to 0 right at the beginning and not reassigning it anywhere, so it cannot be > 200 so the transaction will never roll back.

There's another problem. If that EXISTS returns false, the procedure exits with an uncommitted transaction. Move the Begin Tran inside the IF.

--
Gail Shaw
SQL Server MVP

matta0990
Starting Member

44 Posts

Posted - 2011-05-22 : 14:09:38

Ok, so what should I be setting it to? I did think this but didn't no what else to put?

J Smith

matta0990
Starting Member

44 Posts

Posted - 2011-05-22 : 14:34:23

Fixed it, thanks anyway

J Smith

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-22 : 16:27:42

quote:
Originally posted by matta0990

Ok, so what should I be setting it to? I did think this but didn't no what else to put?

Um, well, wherever the value of BookingRoom is that you're trying to test. You say if 'it' exceeds 200 the transaction must roll back, where and how must 'it' exceed 200?

--
Gail Shaw
SQL Server MVP

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-23 : 06:12:07

Is this run inside a transaction? If so no need for the begin tran here.
If not still no need for the begin tran - just don't do the update instead of rolling it back.

It's a bit odd anyway as there are no parameters passed to the SP - like RoomID.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-23 : 06:25:05

quote:
Originally posted by nigelrivett

It's a bit odd anyway as there are no parameters passed to the SP - like RoomID.

Agreed. There's a fair bit that looks very wrong here.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources