increment of key

• rk-158886

  Newbie

  Points: 2

  More actions

  July 15, 2004 at 3:52 am

  #161684

  I have the model# like 'r001','r002','r003','c001','c002','c003''c004'

  i want to write a storeprocedure which will generate automatically the the next model# depend on the 'r'/'c'

  say next time it has to generate 'r004'/'c005' while running my store peocedure next time

• Johan Bijnens

  SSC Guru

  Points: 134873

  More actions

  July 15, 2004 at 6:55 am

  #514637

  if that is what you need .... create a parameter table in which you keep your last or next value per keytype (r/c/...)

  Isn't it the same for you application/user if you just have one running number (maybe identity / uniqueid) and then add the r or c as an attribute to your data ?

  This way you have a meaningless key which may be more adaptable in the future.

   

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• gljjr

  SSCrazy Eights

  Points: 9970

  More actions

  July 15, 2004 at 1:00 pm

  #514732

  This is going to make your life miserable in the long run.

  As Alzdba stated I would add an identity column to use as an alternate key for table and then use that number along with your modeltype to create the model# value. Otherwise you will need to design a function to parse out the model# into the separate parts and then get the max value used from that, or do as Alzdba stated and use a separate table that you store the last given value for each type in. Niether of these solutions scale very well though.

  Please be advised that using identity columns with replication is a royal pain!

  
  
  
  Gary Johnson
  Microsoft Natural Language Group
  DBA, Sr. DB Engineer

  This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

• Guy Haskell

  Ten Centuries

  Points: 1212

  More actions

  July 15, 2004 at 5:04 pm

  #514776

  If you are stuck with an existing db design, you should be able to do something like this:

  create function fnGetNextModelNo(@type nchar(1)) returns nvarchar(4) as

      begin

          declare @return nvarchar(4), @max-2 int

          select @max-2=max(cast(substring(modelno,2,3) as int)) from models where left(modelno,1)=@type

          set @return=@type+right('000'+cast(@max as nvarchar(3),3)

          return @return

      end

  Then you can do:

  select dbo.fnGetNextModelNo('r') 

  or

  select dbo.fnGetNextModelNo('c')

  to get the next number.

  Be aware that you may need to have some sort of loop catering for duplicate key errors if you are expecting lots of concurrent record insertions.

• Scalability Doug

  SSCertifiable

  Points: 5351

  More actions

  July 16, 2004 at 7:39 am

  #514840

  I had a similar situation and had to go the parameter table route and it was not that difficult. For each model number you will have a "last model id" in which you just need to increment to get the new number for that model. The reason the Identity column did not work for me was this: Each different model type was based on the previous. Model type 1 would go from RD001 to P001. Using the identity column caused gaps. If two models went to RD befor one went to P then the P model lost it's sequence ( RD001, P001, RD002, RD003, P004 ) Wrong! And then the next RD would be RD005 causing a gap and the users to ask what happened to RD004?

  Needed (RD001, P001, RD002, RD003, P002, RD004&nbsp.