Procedure to create unique values

• nikhil.desai1

  SSCommitted

  Points: 1948

  More actions

  July 24, 2010 at 5:52 am

  #141234

  I have One table name Company as Follows

  Create table Company

  (

  ID char(2),

  CompanyID char(1),

  CategoryID varchar(10),

  Primary key(CompanyID, CategoryID)

  )

  I want to make one procedure 'USP_NextID' such as when i pass 'ID' and 'CompanyID'

  as parameter then the value should get inserted into to Company Table.

  Also the categoryid should be auto incremented.

  Consider I have two differennt companies A and B

  When i m logged in Company A and i m executing USP_NextID

  then then categorid should be incremented as follows

  CA001, CA002, CA003

  When i m getting logged in Company B and i m executing USP_NextID

  then then categorid should be incremented as follows

  CA001, CA002, CA003

  when I execute procedure like this USP_NextID 'CA', 'A'

  The values that will be inserted into company table will be

  ID CompanyID CategoryID

  CAACA001

  Example When i execute the procedure

  USP_NextID 'CA', 'A'

  USP_NextID 'CA', 'A'

  USP_NextID 'CA', 'A'

  USP_NextID 'CA', 'B'

  USP_NextID 'CA', 'B'

  USP_NextID 'CA', 'B'

  Then the output of the Table should be

  ID CompanyID CategoryID

  CAACA001

  CAACA002

  BA A CA003

  CA B CA001

  CA B CA002

  CA B CA003

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 24, 2010 at 8:40 am

  #1197861

  Duplicate post. No replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic958374-146-1.aspx

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass