Help needed please on Stored procedure logic

Hi all,
I have the following sproc which is not giving me the results I would
anticipate it should. Basically what the logic needed is that there
are a load of tickets for an event, I need a sproc that holds (by
setting a database flag) any number of tickets the users want, this is
a true multi user environment. This sproc holds the required mount and
if there are not enough tickets then it reports that but in a 2 user
test, say for 4 tickets each, and where there are loads of tickets,
the first user gets 4, the other gets a message saying there are not
enough. Is it to due with viariable sharing or soemthing
The idea was to loop round the database for available tickets, update
the flag and once done commit the transaction, otherwise roll it back
Any thoughts'
Cehers
Shaun
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 = 0
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) < (@.NumberReqd - @.CurrentTicket)
GOTO None_Left
SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
FROM [Event Tickets]
WHERE [Event Tickets].[Ticket Status]='0' AND [Event
Tickets].[Event Id] = @.EventId
ORDER BY [Event Tickets].[Ticket Ref])
UPDATE [Event Tickets]
SET [Ticket status] = 1,
[User Id] = @.UserId,
[Status Updated] = GetDate()
WHERE [Ticket Ref] = @.TicketRef
SET @.CurrentTicket = @.CurrentTicket + 1
CONTINUE
END
COMMIT TRAN BIP
SET @.Resp = 0
RETURN
None_Left:
ROLLBACK TRAN BIP
SET @.Resp = 1
RETURN
GOTicket reservations are extremely complicated. What happens when people
want two seats in one row and three seats in the row behind them? How about
three rows of four seats? As you can see, simply assigning seats by the
order that they appear within your table might not be enough. Anyway, here
is one method that assigns tickets based on your system.
It is probably best to show you an example, so I created a table, some
sample data, and a stored procedure.
/*
I have no idea what datatype TicketStatus is. At one point you use '0'
within a query and later on you use 1 (no quotes).
*/
USE tempdb
GO
CREATE TABLE #EventTickets (TicketRef int, SeatSection varchar(10), SeatRow
varchar(10), SeatNo int,
TicketStatus int, EventID int, ReserveUserID int,
ReserveDate datetime)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (1, 'Floor', '1', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (2, 'Floor', '1', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (3, 'Floor', '1', 3, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (4, 'Floor', '1', 4, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (5, 'Floor', '1', 5, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (6, 'Floor', '2', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (7, 'Floor', '2', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (8, 'Floor', '2', 3, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (9, 'Floor', '2', 4, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (10, 'Floor', '2', 5, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (11, 'Floor', '2', 6, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (12, 'Floor', '2', 7, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (13, '100', '1', 1, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (14, '100', '1', 2, 0, 10)
INSERT INTO #EventTickets (TicketRef, SeatSection, SeatRow, SeatNo,
TicketStatus, EventID)
VALUES (15, '100', '1', 3, 0, 10)
GO
CREATE PROC FindAndReserve
@.UserId int,
@.EventId int,
@.NumberReqd int,
@.Resp int OUTPUT
AS
SET NOCOUNT ON
--select * from #EventTickets
IF EXISTS (SELECT * FROM #EventTickets A WHERE A.TicketStatus = 0)
BEGIN
IF (SELECT COUNT(*) FROM #EventTickets A
WHERE A.TicketStatus = 0
AND A.EventID = @.EventId) < @.NumberReqd
BEGIN
PRINT 'not enough tickets left' /*just for a visual; remove before use in
production*/
SET @.Resp = 1
RETURN (0)
END
DECLARE @.TicketsToReserve TABLE (TicketRef int)
BEGIN TRANSACTION
SET ROWCOUNT @.NumberReqd
/*get a list of tickets that we are going to reserve*/
INSERT INTO @.TicketsToReserve
SELECT TicketRef FROM #EventTickets WHERE TicketStatus = 0 ORDER BY
TicketRef
UPDATE #EventTickets
SET TicketStatus = 1,
ReserveUserID = @.UserId,
ReserveDate = GetDate()
FROM #EventTickets A JOIN @.TicketsToReserve B ON A.TicketRef = B.TicketRef
PRINT 'Reservation complete' /*just for a visual; remove before use in
production*/
SET @.Resp = 0
COMMIT TRANSACTION
RETURN (0)
END
PRINT 'Commpletely sold out' /*just for a visual; remove before use in
production*/
SET @.Resp = 3
GO
----
---
----
---
select * from #EventTickets
go
/*not enough tickets*/
DECLARE @.Result int
exec FindAndReserve @.UserId = 500,
@.EventId = 10,
@.NumberReqd = 1000,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
/*start the reservations!*/
DECLARE @.Result int
exec FindAndReserve @.UserId = 500,
@.EventId = 10,
@.NumberReqd = 2,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 526,
@.EventId = 10,
@.NumberReqd = 4,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 87,
@.EventId = 10,
@.NumberReqd = 3,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 800,
@.EventId = 10,
@.NumberReqd = 4,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 5,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 2,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
go
DECLARE @.Result int
exec FindAndReserve @.UserId = 750,
@.EventId = 10,
@.NumberReqd = 1,
@.Resp = @.Result OUTPUT
SELECT @.Result AS TheResult
go
select * from #EventTickets
/*RESET*/
-- UPDATE #EventTickets SET TicketStatus = 0, ReserveUserID = NULL,
ReserveDate = NULL
-- drop proc FindAndReserve
--
Keith
"Shaun" <shaunsizen@.msn.com> wrote in message
news:4a2f9143.0411160632.1c24a330@.posting.google.com...
> Hi all,
> I have the following sproc which is not giving me the results I would
> anticipate it should. Basically what the logic needed is that there
> are a load of tickets for an event, I need a sproc that holds (by
> setting a database flag) any number of tickets the users want, this is
> a true multi user environment. This sproc holds the required mount and
> if there are not enough tickets then it reports that but in a 2 user
> test, say for 4 tickets each, and where there are loads of tickets,
> the first user gets 4, the other gets a message saying there are not
> enough. Is it to due with viariable sharing or soemthing
> The idea was to loop round the database for available tickets, update
> the flag and once done commit the transaction, otherwise roll it back
> Any thoughts'
> Cehers
> Shaun
>
> 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 = 0
>
> 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) < (@.NumberReqd - @.CurrentTicket)
> GOTO None_Left
> SET @.TicketRef = (SELECT TOP 1 [Ticket Ref]
> FROM [Event Tickets]
> WHERE [Event Tickets].[Ticket Status]='0' AND [Event
> Tickets].[Event Id] = @.EventId
> ORDER BY [Event Tickets].[Ticket Ref])
> UPDATE [Event Tickets]
> SET [Ticket status] = 1,
> [User Id] = @.UserId,
> [Status Updated] = GetDate()
> WHERE [Ticket Ref] = @.TicketRef
> SET @.CurrentTicket = @.CurrentTicket + 1
> CONTINUE
> END
> COMMIT TRAN BIP
> SET @.Resp = 0
> RETURN
> None_Left:
> ROLLBACK TRAN BIP
> SET @.Resp = 1
> RETURN
> GO