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

From: Shaun (shaunsizen_at_msn.com)
Date: 11/02/04 

Date: 2 Nov 2004 08:24:48 -0800

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

Relevant Pages

  • Help needed please on Stored procedure logic
    ... I have the following sproc which is not giving me the results I would ... setting a database flag) any number of tickets the users want, ... Declare @CurrentTicket Integer ... COMMIT TRAN BIP ...
    (microsoft.public.sqlserver.server)
  • Re: How much detail for N400?
    ... resulted in tickets. ... Sounded to me like he was given a standard leaflet. ... Local guy failed to declare a 18 youthful misdemeanor on his N400 since ... traffic control device. ...
    (misc.immigration.usa)
Loading