June 26, 2009 at 2:29 am
I have got following requirement which should be handled in SP,
1.I need to generate a Alpha numeric values in a sequence when I insert records to a column in the table.
a.Alpha Numeric value should be in the format like ‘JV1 , JV2 and so on.
b.Numeric value should be always appended with varchar – ‘JV’, followed by sequence Number starting with 1
2.When I insert new records for the next time, I should be checking for the existing max sequence value and generate next sequence value appended with varchar- ‘JV’ like ‘JV3’.
3.Single table will be always targeted to insert and fetch the alphanumeric values.
4.The table will have multiple records with the same alphanumeric value.
5.The targeted column can have other values like ‘INV001’,’CRN001’ etc
June 26, 2009 at 3:04 am
Manually creating your own sequence numbers has a lot of potential pitfalls.
There was a thread a few days ago regarding a similar issue, that you should read.
http://www.sqlservercentral.com/Forums/Topic737659-1291-1.aspx
The response from John Rowan in that thread also points to another thread you may want to follow.
June 26, 2009 at 3:12 am
It sounds like you're effectively trying to store several different Primary Keys in one table, which isn't the wisest thing to attempt. A table for each type of transaction would make more sense and then let SQL Server look after the ID.
However, sometimes we have to work with what we have, no matter how bad it may be, so another approach may be to use a Computed Column, where the transaction type (JV, INV, whatever) is one column, the sequence number is another column and the Computed Column is the two together.
For a description of Computed Columns - http://www.sqlservercentral.com/articles/T-SQL/61764/
BrainDonor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply