Help ! Nested Stored Procedure, is this the best way???

Hi all,
Excuse my lack of knowledge in this area but what I am trying to do is
1) The user requests that say 4 tickets are to be held
2) The VB program calls a sproc that executes and tries to update the
status of each ticket in turn
3) If 4 are available then all is dandy
4) If 4 are not then any updates are rolled back and the user notifed
Note this is a true multi user environment, the tickets don't have to
be locked in sequence, they are just places on an event
Tried this but got odd rollback messages like
Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 6, current count =
7.
Line 23 is the return statement
SQL 2000 is the beast in use
HELP!! Ideas
Sproc is
CREATE PROCEDURE BIP_Tickets @.UserId Integer,
@.EventId Integer,
@.NumberReqd as Integer,
@.Resp Integer OUTPUT AS
SET NOCOUNT ON
Declare @.CurrentTicket Integer
Declare @.TicketRef Integer
SET @.CurrentTicket = 1
BEGIN TRAN BIP
WHILE @.CurrentTicket < @.NumberReqd
BEGIN
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId)
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
END
COMMIT TRAN BIP
SET @.Resp = 0
GO"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> Hi all,
> Excuse my lack of knowledge in this area but what I am trying to do is
> 1) The user requests that say 4 tickets are to be held
> 2) The VB program calls a sproc that executes and tries to update the
> status of each ticket in turn
> 3) If 4 are available then all is dandy
> 4) If 4 are not then any updates are rolled back and the user notifed
> Note this is a true multi user environment, the tickets don't have to
> be locked in sequence, they are just places on an event
> Tried this but got odd rollback messages like
> Server: Msg 266, Level 16, State 2, Procedure BIP_Tickets, Line 23
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 6, current count =
> 7.
> Line 23 is the return statement
> SQL 2000 is the beast in use
> HELP!! Ideas
> Sproc is
>
Your immediate problem is that TSQL's IF only affects the next statement.
You wrote
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId) < 1
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
which is equivilent to
IF (SELECT COUNT(*) FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tickets].&#
91;Event
Id] = @.EventId) < 1
BEGIN
ROLLBACK TRAN BIP
END
SET @.Resp = 1
RETURN
So you are always returning at line 23.
You probably have some more concurrency problems after fixing this, so test
and post again when you get deadlocks or double bookings.
David|||Cheers David, good point, not used the IF too much and it was always
single statement before
I'll rework, give it a go and post back if necessary
Thanks again
Shaun
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message news:<OqHo#4P
wEHA.3108@.TK2MSFTNGP14.phx.gbl>...
> "Shaun" <shaunsizen@.msn.com> wrote in message
> news:4a2f9143.0411020824.24ac7479@.posting.google.com...
> Your immediate problem is that TSQL's IF only affects the next statement.
> You wrote
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId) < 1
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> which is equivilent to
>
> IF (SELECT COUNT(*) FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event Tick
ets].[Event
> Id] = @.EventId) < 1
> BEGIN
> ROLLBACK TRAN BIP
> END
> SET @.Resp = 1
> RETURN
> So you are always returning at line 23.
> You probably have some more concurrency problems after fixing this, so tes
t
> and post again when you get deadlocks or double bookings.
> David