Auto Increment ID

Try like below:

DECLARE @tempESPL VARCHAR(10);
SET @tempESPL = 'ESPL' + Right(Year(getDate())+ 1,2) + right('0000'+convert(varchar(4), id), 4); -- Id is incremental values like 1,2,3,4,
PRINT @tempESPL;

+1

Ohkay... Just Display current year instead of '17'

+2

Sir

Next to ESPL Needed Year Last two digits

0

Go through the following links:

http://www.webcodeexpert.com/2015/08/auto-generate-auto-incremented-unique.html

http://www.c-sharpcorner.com/UploadFile/ff2f08/auto-incremented-column-with-varchar-and-nvarchar-data-type/

+1

You can use following query and you can manage your user defined incremented ID....

declare @no as varchar(10); --declare a variable
set @no = 'ESPL170001'; --this value can be stored from anywhere even from any table
select concat ('ESPL',(cast(SUBSTRING(@no, 5, len(@no)) as int)+1)) as Id

0