Showing posts with label sproc. Show all posts
Showing posts with label sproc. Show all posts

Friday, March 30, 2012

Help on using LIKE in sproc

hi all,

I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details

My Table (JournalArticle)
ArticleID - int (PK)
ArticleTitle - varchar
ArticleContent - text

I could run a normal sql string against the table itself in ASP.NET and got the results I expect.
but when using a sproc, i couldn't get anything
The sproc


CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch text)
AS SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE @.srch)
GO

After reading some of the threads here, I experimented by changing ArticleContent and @.srch to type varchar, still no luck, it's not returning anything.
I think the problem is when i set the value of @.srch (being new at this, I could be seriously wrong though), like this:

prmSearch.ParameterName = "@.srch"
prmSearch.SqlDbType = SqlDbType.Text
prmSearch.Value = Request.Form("txtSearch")

My original string looks like this

strSQL = "SELECT * FROM JournalArticle WHERE (ArticleContent LIKE '%" & Request.Form("txtSearch") & "%')"

What am I doing wrong?? Thanks in advance for any help.Hey, I'm new to sprocs but I have never seen the first line like that. i usually write them and seen them liek this;

CREATE PROCEDURE dbo.sp_ArticleSearch
@.srch text
as...

If that isn't the problem then could it be the LIKE part? Should it not have '%' too? or some other char for t-sql?

I may not know the correct answer, but try it and let me know, cuz i too am in the learning process and will use statements like that in the furture.|||You absolutely need to add the % wildcards to get this work for you the way it used to work.

You either need to add the '%' to the @.srch parameter:


CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch text)
AS
SET NOCOUNT ON
SELECT @.srch = '%' + @.srch + '%'
SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE @.srch)
GO

Or you need to append the wildcards directly in the SQL statement

CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch text)
AS
SET NOCOUNT ON
SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE '%' + @.srch + '%')
GO

It seems the general consensus is that the first option is the best way to go. Note that I added SET NOCOUNT ON to your sproc. This is especially necessary for the first sproc since without it SQL will send something like '1 item selected' as the first resultset back to your ASP.NET page.

Terri|||Thanks guys, will give this a try later.|||Terri, still no luck,

both of the sample procedures gave error:

Error 403 Invalid Operator For Data Type. Operator Equals Add. Type equals Text.

I've absolutely no idea what this means.|||OK, I got this to work if the parameter type is set to 'varchar' instead of 'text' in the stored procedure itself so it'll look like this:


CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch varchar(8000))
AS
SET NOCOUNT ON
SELECT *
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE '%' + @.srch + '%')
GO

Thank you very much guys.|||I found that error to be is data-type mismatch. Is the field 'ArticleAbstract' data-type text? as u declared @.srch to be?|||heh, u beat me to itsql

Monday, March 19, 2012

Help needed with this sproc

Hi, I am trying to Implement Multi parameter...

If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'.

This is my sproc

ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]@.ClientIdnvarchar(max)=NULL,@.StartDatedatetime,@.EndDatedatetimeASDeclare @.SQLTEXT nvarchar(max)If @.ClientIdISNULLBeginSelect o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInnerJoin ClientPlan cpon o.PlanId = cp.PlanIdInnerJoin ClientUser con o.CreatedByUserId = c.UserIdWHERE--cp.ClientId = @.ClientId--AND o.OrderDateBETWEEN @.StartDateAND @.EndDateORDER BYo.OrderIdDESCENDELSEBEGIN SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' + @.ClientId +')AND o.OrderDate BETWEEN ' +Convert(varchar,@.StartDate) +' AND ' +convert(varchar, @.EndDate) +' ORDER BYo.OrderId DESC'execute (@.SQLTEXT)END

any help will be appreciated.

Regards

Karen

Try this:

SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' +Convert(Varchar,@.ClientId) +')AND o.OrderDate BETWEEN ' +Convert(varchar,@.StartDate) +' AND ' +convert(varchar, @.EndDate) +' ORDER BYo.OrderId DESC'Exec(@.SQLTEXT)

If you still have errors, post exactly how you are calling the proc.

|||

i am first execting the stored proc using query analyser and this is how i am calling it

usp_GetOrdersByOrderDate'7,4','12/17/2007','12/20/2007'

|||

ahh.. you need quotes around the dates too..

SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' +Convert(Varchar,@.ClientId) +')AND o.OrderDate BETWEEN ''' +Convert(varchar,@.StartDate) +''' AND ''' +convert(varchar, @.EndDate) +''' ORDER BYo.OrderId DESC'
|||

thanks,,,

Yes that did it.

Regards

Karen

Monday, March 12, 2012

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

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

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
GO
Ticket 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.c om...
> 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