none
Generate a Alphanumeric ID

    Question

  • Hello Folks,

    I have to create a stored procedure to generate 14 character alphanumeric id in this format:

    1. First three character would be static, passed as the parameter from stored procedure
    2. Next 6 character would be the current date in yymmdd format
    3. Last last five character would be the number form 1 to 99999

    And I more thing I have to reset the number portion on daily basis, when the new date comes up the number should start from 1.

    For example: If I pass three character in the stored procedure like "GLT" the ID would be GLT13071000001 and next time if I pass "XYZ" then id would be XYZ13071000001, again if I pass "GLT" the id would be GLT13071000002.

    Thanks


    Amar Deep Singh

    Wednesday, July 10, 2013 10:09 PM

Answers

  • I have to create a stored procedure to generate 14 character alphanumeric id in this format:

    1. First three character would be static, passed as the parameter from stored procedure
    2. Next 6 character would be the current date in yymmdd format
    3. Last last five character would be the number form 1 to 99999

    And I more thing I have to reset the number portion on daily basis, when the new date comes up the number should start from 1.

    One method below.  The purpose of the transaction and serializable level is to ensure concurrent executions for the same prefix do not get the same sequence number and/or result in a primary key violation.

    CREATE TABLE dbo.sequence_string(
    	prefix char(3) NOT NULL
    	,yymmdd char(6) NOT NULL
    	,sequence_number int NOT NULL
    	CONSTRAINT pk_sequence_string PRIMARY KEY(prefix, yymmdd)
    	);
    GO
    
    CREATE PROC dbo.usp_get_sequence_string
    	@prefix char(3)
    AS
    SET XACT_ABORT ON;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    
    BEGIN TRAN;
    
    DECLARE
    	@sequence_number int = 1
    	,@yymmdd char(6) = RIGHT(CONVERT(char(8), GETDATE(), 112), 6);
    
    UPDATE dbo.sequence_string
    SET @sequence_number = sequence_number = sequence_number + 1
    WHERE
    	prefix = @prefix
    	AND yymmdd = @yymmdd;
    
    IF @@ROWCOUNT = 0
    BEGIN
    	--first for today - insert sequence_number 1 and remove other days
    	INSERT INTO dbo.sequence_string VALUES(@prefix, @yymmdd, @sequence_number);
    	DELETE FROM dbo.sequence_string
    	WHERE
    		prefix = @prefix
    		AND yymmdd <> @yymmdd;
    END;
    
    COMMIT;
    
    SELECT @prefix + @yymmdd + RIGHT('0000' + CAST(@sequence_number AS varchar(5)), 5) AS sequence_string;
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, July 11, 2013 3:38 AM
  • Thanks you very much Dan :), your solution worked.

    Amar Deep Singh

    Thursday, July 11, 2013 4:16 PM

All replies

  • Can you post DDL and sample data.

    In brief, the procedure will have to look into the table where the alphanumerics are stored. Then only it can start a new number combination based the three digit character passed.

    Thursday, July 11, 2013 12:34 AM
  • How about this?  It requires a new table to track the ids, so depending on how you will use the ids you might want to integrate with an existing table.
    CREATE TABLE dbo.IdList (IdId INT IDENTITY, DateCreated DATE) --indexes needed
    GO
    
    ALTER PROCEDURE dbo.prc_CreateId 
    @CharSeed CHAR(3),
    @Id VARCHAR(14) OUTPUT
    AS
    BEGIN
    
    	DECLARE @IdId INT, @Num INT = 1, @Today DATE = GETDATE()
    
    	INSERT INTO dbo.IdList(DateCreated) VALUES (@Today)
    	SELECT @IdId = SCOPE_IDENTITY()
    
    	SELECT @Num = COUNT(*) FROM IdList WHERE DateCreated = @Today AND IdId <= @IdId
    
    	IF LEN(@Num) > 5
    		RAISERROR ('No more Ids left for today, try again tomorrow',16, 1)
    
    	SET @Id = @CharSeed + CONVERT(VARCHAR(6),GETDATE(), 12) + REPLACE(SPACE(5-len(@NUM)),' ','0') + CAST(@Num AS VARCHAR)
    END
    GO
    
    DECLARE @Id VARCHAR(14)
    EXEC dbo.prc_CreateId 
    	@CharSeed  = 'abc',
    	@Id = @Id OUTPUT
    SELECT @Id


    Thursday, July 11, 2013 1:08 AM
  • I have to create a stored procedure to generate 14 character alphanumeric id in this format:

    1. First three character would be static, passed as the parameter from stored procedure
    2. Next 6 character would be the current date in yymmdd format
    3. Last last five character would be the number form 1 to 99999

    And I more thing I have to reset the number portion on daily basis, when the new date comes up the number should start from 1.

    One method below.  The purpose of the transaction and serializable level is to ensure concurrent executions for the same prefix do not get the same sequence number and/or result in a primary key violation.

    CREATE TABLE dbo.sequence_string(
    	prefix char(3) NOT NULL
    	,yymmdd char(6) NOT NULL
    	,sequence_number int NOT NULL
    	CONSTRAINT pk_sequence_string PRIMARY KEY(prefix, yymmdd)
    	);
    GO
    
    CREATE PROC dbo.usp_get_sequence_string
    	@prefix char(3)
    AS
    SET XACT_ABORT ON;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    
    BEGIN TRAN;
    
    DECLARE
    	@sequence_number int = 1
    	,@yymmdd char(6) = RIGHT(CONVERT(char(8), GETDATE(), 112), 6);
    
    UPDATE dbo.sequence_string
    SET @sequence_number = sequence_number = sequence_number + 1
    WHERE
    	prefix = @prefix
    	AND yymmdd = @yymmdd;
    
    IF @@ROWCOUNT = 0
    BEGIN
    	--first for today - insert sequence_number 1 and remove other days
    	INSERT INTO dbo.sequence_string VALUES(@prefix, @yymmdd, @sequence_number);
    	DELETE FROM dbo.sequence_string
    	WHERE
    		prefix = @prefix
    		AND yymmdd <> @yymmdd;
    END;
    
    COMMIT;
    
    SELECT @prefix + @yymmdd + RIGHT('0000' + CAST(@sequence_number AS varchar(5)), 5) AS sequence_string;
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, July 11, 2013 3:38 AM
  • Try this,

    CREATE PROCEDURE GENERATEID(@INITIAL NVARCHAR(10))
    AS
    BEGIN
    	DECLARE @ID NVARCHAR(50)
    	DECLARE @COUNT INT
    	SET @COUNT=1
    	
    	WHILE @COUNT <= 99999
    	BEGIN
    			SET @ID= @INITIAL + CAST(RIGHT(YEAR(GETDATE()),2) AS NVARCHAR) + 
    			REPLICATE(0,2 - DATALENGTH( CAST(MONTH(GETDATE()) AS VARCHAR))) + CAST(MONTH(GETDATE()) AS VARCHAR) + 
    			REPLICATE(0,2 - DATALENGTH( CAST(DAY(GETDATE()) AS VARCHAR))) + CAST(DAY(GETDATE()) AS VARCHAR) +
    			REPLICATE(0,5 - DATALENGTH( CAST(@COUNT AS VARCHAR))) + CAST(@COUNT AS VARCHAR)
    			SET @COUNT=@COUNT+1
    			PRINT @ID
    	END
    END
    --------------------------
    EXEC GENERATEID 'ABC'
    EXEC GENERATEID 'XYZ'


    Regards, RSingh

    Thursday, July 11, 2013 4:01 AM
  • I think you would be better of not storing those values in the table but by using ROW_NUMBER /or REPLICATE function generate  the output to the client, something like that

    create table #t (id varchar(100))

    insert into #t values ('GTL'),('GTL'),('ABC'),('ABC'),('ABC'),('XYZ')



    SELECT cast( ID as varchar(10) )+left( replicate( '0', 10 ), 
    9 - len( ID ) ) + cast(ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) as varchar(10))
    FROM #T


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, July 11, 2013 1:19 PM
    Answerer
  • I would highly suggest using 3 seperate fields for that kind of data, and then combining it on the display side.  It will be much easier to create and search.

    Thursday, July 11, 2013 1:24 PM
    Moderator
  • I am highly appreciates the replies from you guys, let me try one by one and will let you know, which one fits on my requirement.


    Amar Deep Singh

    Thursday, July 11, 2013 3:54 PM
  • Hello SDeering,

    I tried your solution but there is one problem in that, First time I passed 'ABC' then its gives me the ID ABC13071100001 and then I passed 'XYZ' then it gives me XYZ13071100002.

    The second ID would start from 1 because the prefix was different.

    I hope you got my question.

    Thanks


    Amar Deep Singh

    Thursday, July 11, 2013 4:02 PM
  • Hello RSingh,

    Your query gives me the IDs from 1 to 99999, but that's not my requirement, I wanted to get a single ID when I execute the stored proc.

    BTW thanks.


    Amar Deep Singh

    Thursday, July 11, 2013 4:11 PM
  • Thanks you very much Dan :), your solution worked.

    Amar Deep Singh

    Thursday, July 11, 2013 4:16 PM