coming up with a routine

[Kenny M.]

hi

I have a DB which receive tickets in secuence 1,2,3,4,5.... I want to
create an routine that can detect if a ticket is missing so I just need to
detect if the secuence is broken (1,2,3,5... 4 is missing)

I want to create a good routine and I can only thing about a for loop with
IF THEN inside

Any one have an idea?

Can I do it with an Store procedure insted a C# code?

--
Kenny M.

[Ken Dopierala Jr.]

Hi Kenny,

I would instead have my table use an identity field for the ticket number
that auto-increments by 1. This way you would never have a number missing
from the sequence. Also in the table create a field for a flag as to
whether a ticket was cancelled or something. Then you can do a quick query
for all non-valid tickets just by checking this field. Good luck! Ken.

--
Ken Dopierala Jr.
For great ASP.Net web hosting try:
http://www.webhost4life.com/default.asp?refid=Spinlight
If you sign up under me and need help, email me.

"Kenny M." <Ke****@discussions.microsoft.com> wrote in message
news:9D**********************************@microsof t.com...

hi

I have a DB which receive tickets in secuence 1,2,3,4,5.... I want to
create an routine that can detect if a ticket is missing so I just need to
detect if the secuence is broken (1,2,3,5... 4 is missing)

I want to create a good routine and I can only thing about a for loop with
IF THEN inside

Any one have an idea?

Can I do it with an Store procedure insted a C# code?

--
Kenny M.

[Jeff Louie]

Kenny... The first question is Is it worth optimizing this step? If the
answer is
yes, then you could possibly speed up the processing by returning a
sorted
sequence and writing your own algorithm doing a binary search looking
for
row number > ticket number where row number and ticket number are one
based.

This begs the question if your DB truly is guaranteed to create a
sequence.
Identity does _not_ quarantee a valid sequence. "The value automatically
produced with the Identity .... is not guaranteed to be consecutive. For
efficiency, a value is considered used as soon as it is presented to a
client."

So if a client starts an INSERT and then rolls back on error the number
is
_lost_ and a gap appears. If you want a consecutive sequence consider a
"next_number" table.

As for SQL.
http://www.developersdex.com/gurus/articles/115.asp
http://www.nigelrivett.net/FindGapsInSequence.html
http://www.tek-tips.com/faqs.cfm?fid=840

Finding gaps in an identity column sequence

Suppose a table has an identity column and gaps exist in the identity
sequence. Suppose you want to identify the gaps and fill them in. (I
don't
know why one would want to do this but we are just supposing. ) The
following T-SQL code will find the next gap in the sequence.

Create Procedure usp_FindNextPartID As

Declare @MinID int
Select @MinID = Min(PartID) From PartTable

If @MinID > 1
**Select MinID=1
Else
**Select MinID=min(a.PartId+1)
***From PartTable a Left Join PartTable b
*********On a.PartId+1=b.PartId
****Where b.PartNumber Is Null
Go
Regards,
Jeff

I have a DB which receive tickets in secuence 1,2,3,4,5.... I want to

create an routine that can detect if a ticket is missing so I just need
to
detect if the secuence is broken (1,2,3,5... 4 is missing)<
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!