Generate Next Numbers with SQL Server

Question

Generate Next Numbers with SQL Server

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jim Underwood Mr or Mrs. 500 Points: 593 More actions · Answer 1

I find this article unreadable.

The very wide image causes the text to spread out so far to the right that excessive horizontal scrolling is needed. So, as you read each line, you have to scroll back and forth.

Extremely poor layout.

Did you review the layout before you published it?

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 2

Jim Underwood ,

I also found it unreadble. I think this formatting is introduced when publishing the artcle.

My Blog: http://dineshasanka.spaces.live.com/

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 3

I found some different ratings to this articles.

It will be better to get a feed back rather than just a rating.

Is there any way that I can see who rated this and what is the ratings.

My Blog: http://dineshasanka.spaces.live.com/

dogeth Mr or Mrs. 500 Points: 576 More actions · Answer 4

Hey good idea Antares686

update tblName set @var = col = col + 1

I'll definitely use this method from now on. No explicit transaction, no explicit locking... Thanks!

Cheers,

Kevin

johncyriac SSCommitted Points: 1542 More actions · Answer 5

Hay you are doing good job but consider my suggestion also,the discussion is moving away from the topic "Generate Next Numbers with SQL Server"

CREATE TABLE TBL_SEQ_PATTERNS

(

SEQ_TYPE VARCHAR(10),

SEQ_PATTERN VARCHAR(100), -- YYYY####

SEQ_RESET VARCHAR(10), --(‘DAILY,’‘MONTHLY’,’YEARLY’,’NEVER’)

SEQ_LAST INTEGER

)

Then consider a pattern like YYYY#### means 20040001, 20040002 , ……

Then Then consider another like YYMM#### means 04030001, 04030002 , ……

SEQ_RESET filed may take values (‘DAILY,’‘MONTHLY’,’YEARLY’,’NEVER’),

Then all others are left to your imagination ,

But I would like to know on your response on my suggestion

regards

john

Coutant SSC-Addicted Points: 491 More actions · Answer 6

Consider this approach...

Three tables with an identity field for each table.

When you add an invoice, add a record to the invoice table, then use the identity from the invoive table (IDENT_SEED) with your required prefix in the table that you actually want to store the data.

Same goes for the other two tables.

When you need to reset your values, a scheduled job truncates the invoice table (that is used to simply generate the identity values) which resets the seed to 1.

BTW, for the date portion: RIGHT(YEAR(GETDATE()), 2) + RIGHT('0' + MONTH(GETDATE()), 2) will give you the prefix without having to do the IF statement.

Scott

coutant@comcast.net

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 7

Thankx Coutant and johncyriac

Certainly there is space for improvements.

But I just want to presenmt my idea

Thankx a lot for ur comments

My Blog: http://dineshasanka.spaces.live.com/

Gerald Schanz SSC Journeyman Points: 98 More actions · Answer 8

Why don't you let the database structure handle this with additional fields for date, type of entity (order, invoice, etc.), unique id(generated from SQL Server)? The unique entity number can then be derived from the elements after the unique id is returned. Your one-off-approach-for -this-client may prove difficult to maintain and likely to cause issues later on. What if the table is corrupted, how do you reset the table value?