title: Creating Autoincrement on AlphaNumeric in Stored Procedure
body: <p>My Table:</p>
  
  <pre><code>CREATE TABLE USER_DATA
  ([ID] NVARCHAR(10),[NAME] NVARCHAR(50))
  </code></pre>
  
  <p>My Stored Procedure:</p>
  
  <pre><code>CREATE PROC SP_INSERT_USER
  @NAME VARCHAR(50)
  AS
  BEGIN
  DECLARE @ID NVARCHAR(50)
  DECLARE @IDTEMP INT
  SELECT @IDTEMP=MAX(CAST(RIGHT([ID],3) AS INT) ) FROM USER_DATA
  SELECT @IDTEMP=CASE WHEN @IDTEMP IS NULL THEN 1 ELSE  @IDTEMP+1 END 
  SELECT @ID='RP'+RIGHT(('000'+CAST(@IDTEMP AS VARCHAR(10))),3)
  INSERT INTO  USER_DATA VALUES (@ID,@NAME)
  END
  </code></pre>
  
  <p>Input:</p>
  
  <pre><code>SP_INSERT_USER  'Ajit'
  SP_INSERT_USER 'Amit'
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>ID         NAME
  ---------- --------------------------------------------------
  RP001     Ajit
  RP002     Amit
  </code></pre>
  
  <p>This code is working fine. But the ID is fixed. It is limited to 3 digits like 001, 002, etc. If it get 1000th employee, it is not displaying properly. Is there any way to make it dynamical without fixing the length?</p>