CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
Pearl
Pearl

Reputation: 9435

Creating Autoincrement on AlphaNumeric in Stored Procedure

My Table:

CREATE TABLE USER_DATA
([ID] NVARCHAR(10),[NAME] NVARCHAR(50))

My Stored Procedure:

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

Input:

SP_INSERT_USER  'Ajit'
SP_INSERT_USER 'Amit'

Output:

ID         NAME
---------- --------------------------------------------------
RP001     Ajit
RP002     Amit

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?

Upvotes: 1

Views: 2983

Answers (1)

Devart
Devart

Reputation: 121922

Try this one -

CREATE TABLE dbo.USER_DATA
(
      PK INT IDENTITY(1,1) PRIMARY KEY
    , ID AS 'PR' + RIGHT('00' + CAST(PK AS VARCHAR(5)), 5)
    , NAME NVARCHAR(50)
)

GO

CREATE PROC dbo.SP_INSERT_USER

    @NAME VARCHAR(50)

AS BEGIN

    INSERT INTO dbo.USER_DATA 
    VALUES (@NAME)

END

GO

EXEC dbo.SP_INSERT_USER 'Ajit'
EXEC dbo.SP_INSERT_USER 'Amit'

SELECT ID, NAME 
FROM dbo.USER_DATA

Upvotes: 1

Related Questions