Generate unique id in a sequence in SQL Server

amitsinghr says:

dear sir i want to genrate ID form my Customer table 

like 

Cust1302170001

where cust is constant 130217 represent today date 

0001 this is customer number

 

this i want to auto increment when customer data is inserted 

using stored procedure 

 

Hi amitsinghr,

While saving you need to check the existence of Customer count with today’s date and add plus one with Constant string along with current count value plus one. You need to handel it while saving as it belong to current count logic

Hi amitsinghr,

Refer the below test query for your referee implement it by using stored procedure. Where you can set Customer Id and at insert time you can pass it to related Table.

SQL

-- CREATED TEMPARARY TABLE
DECLARE @TESTTable AS TABLE(customerId VARCHAR(20),NAME VARCHAR(50))

INSERT INTO @TESTTable VALUES('Cust1502170001','Name 1')
INSERT INTO @TESTTable VALUES('Cust1502170002','Name 2')
INSERT INTO @TESTTable VALUES('Cust1502170003','Name 3')
INSERT INTO @TESTTable VALUES('Cust1502170004','Name 4')
INSERT INTO @TESTTable VALUES('Cust1502170005','Name 5')
INSERT INTO @TESTTable VALUES('Cust1402170001','Name 1')
INSERT INTO @TESTTable VALUES('Cust1402170002','Name 2')
INSERT INTO @TESTTable VALUES('Cust1402170003','Name 3')
INSERT INTO @TESTTable VALUES('Cust1302170001','Name 1')
INSERT INTO @TESTTable VALUES('Cust1302170002','Name 2')
INSERT INTO @TESTTable VALUES('Cust1202170001','Name 1')
INSERT INTO @TESTTable VALUES('Cust1102170001','Name 1')

-- Declare currentdate in variable @DateTime
DECLARE @DateTime DATETIME
SET @DateTime = (SELECT GETDATE())
--SET @DateTime = '2017-02-14 12:13:48.407'
--SET @DateTime = '2017-02-13 12:13:48.407'
--SET @DateTime = '2017-02-12 12:13:48.407'
--SET @DateTime = '2017-02-11 12:13:48.407'
--SET @DateTime = '2017-02-10 12:13:48.407'

-- FROM Here test quey is started
DECLARE @CustomerId VARCHAR(20)
DECLARE @LastCustomerId VARCHAR(5)
DECLARE @Day AS VARCHAR(2) -- to store day valeue
DECLARE @Month AS VARCHAR(2) -- to store month value
DECLARE @Year AS VARCHAR(4) -- to store year value
DECLARE @CureectDatecustomerCount VARCHAR(5)
DECLARE @customerTextPrefix VARCHAR(20) -- It will store CustomerId Prefix text
DECLARE @customerTextNamePrefix VARCHAR(20) -- to store CustomerPrefix text 
SET @customerTextNamePrefix = 'Cust' -- starting value for customerPrefix text

SET @Day = RIGHT('0'+cast(DATEPART(DD,@DateTime) AS varchar(2)), 2)  -- to store Day vlaue
SET @Month = RIGHT('0'+cast(DATEPART(MM,@DateTime) AS varchar(2)), 2) -- to store month value
SET @Year = (DATEPART(YY,@DateTime)) -- Store Year Value

-- concatinate the all string also get only last two value from @year variable
SET @customerTextPrefix = @customerTextNamePrefix + @Day + @Month + SUBSTRING(@Year,(LEN(@Year) - 2 + 1),2)
--match it with the count matching with prefix value and add pluse one in it so it always give next prefix number

-- Check existance of record if exist in table
IF EXISTS(SELECT customerId
		  FROM @TESTTable WHERE customerId
		  LIKE @customerTextPrefix +'%')
	BEGIN
		  -- select last customerId value only last 4 number from last customerid by CurrentDate prefix value
		  SELECT TOP 1  @LastCustomerId = substring(customerId,(LEN(customerId) - 3),4) + 1
		  FROM @TESTTable
		  WHERE customerId LIKE @customerTextPrefix +'%'
		  order by substring(customerId,(LEN(customerId) - 3),4) DESC
		  
		  -- SET customer Id with CurrentDate prefix value
		  SET @CustomerId = @customerTextPrefix + RIGHT('0000'+cast(@LastCustomerId AS varchar(4)), 4)
	END
ELSE
	BEGIN
		-- SET customer Id first CustomerId for with CurrentDate prefix value 
		SELECT @CustomerId = @customerTextPrefix + '0001'	
	END

-- Use the @CustomerId variable value as to display or to insert in any statement
SELECT @CustomerId

Screenshot