Auto Generated No in SQL Stored Procedure

• Abu-560637

  SSC Veteran

  Points: 271

  More actions

  December 6, 2007 at 12:55 am

  #177234

  Hai

  Can any one help me in this regarding..

  I have stored procedure to insert a record,

  auto no is generated through IDENTITY

  What i need is i want to auto generate a No for e.g(PolicyNo) with some format as P00001 where the '1' should be the auto generated no or IDENTITY

  Thankx in advance.

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  December 6, 2007 at 1:44 am

  #757566

  Use this...

  DECLARE @Identity SMALLINT

  SET @Identity =1

  SELECT 'P' + LEFT('00000', 5 - LEN( CONVERT( VARCHAR(5), @Identity ) ) ) + CONVERT( VARCHAR(5), @Identity )

  SET @Identity =9856

  SELECT 'P' + LEFT('00000', 5 - LEN( CONVERT( VARCHAR(5), @Identity ) ) ) + CONVERT( VARCHAR(5), @Identity )

  SET @Identity =15423

  SELECT 'P' + LEFT('00000', 5 - LEN( CONVERT( VARCHAR(5), @Identity ) ) ) + CONVERT( VARCHAR(5), @Identity )

  --Ramesh
  

  ---

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  December 6, 2007 at 1:56 am

  #757569

  Or another solution is to use:

  'P' + RIGHT(REPLICATE('0', 10) + RTRIM(MAX(t.a) + 1), 9)

  This finds that highest id in the table, adds one to it, adds 0s, and prefixes it with 'P'. The result is a string with 10 characters

  Test code: (comment out some insert lines to see different values)

  DECLARE @testTable TABLE ( a INT PRIMARY KEY )

  INSERT INTO @testTable VALUES ( 1 )

  INSERT INTO @testTable VALUES ( 20 )

  INSERT INTO @testTable VALUES ( 300 )

  SELECT 'P' + RIGHT(REPLICATE('0', 10) + RTRIM(MAX(t.a) + 1), 9)

  FROM @testTable AS t

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• Abu-560637

  SSC Veteran

  Points: 271

  More actions

  December 6, 2007 at 6:19 am

  #757622

  Thanks a Lot Andras

  You have saved my lots of time..

  thanks ramesh even your code helped me for some other problems..