How to AutoIncrement after ENQ000000099 it should be ENQ000000100

Question

ENQ000000099 need output of ENQ000000100

Alter function EnqID()
Returns char(12)
As 
Begin 

    Declare @lastval int
    declare @nextenqid nvarchar(30)

    Declare @count int

    set @count=(select count(enqid) from ENQ$Header)
      if @count <=0       
      begin
       set @nextenqid='enq00000001'
      end
      else
      begin
        set @lastval =(select max(cast(right(enqid,7) as int)) from ENQ$Header)
        if @lastval is null set  @lastval = 0000000
    set @nextenqid='ENQ' + Right('0000000' + convert(varchar(12),(@lastval + 1)),12 ) 
    end
    return @nextenqid 
    end

Answer 1

Try this, I have added comments. Please discuss if and query.

Alter function EnqID()
Returns char(12)
As 
Begin 
    Declare @lastval int
    Declare @nextenqid nvarchar(30)

    Declare @count int

    SET @count = (SELECT COUNT(enqid) FROM ENQ$Header)
    If @count = 0
    Begin
        SET @nextenqid = 'ENQ00000001'
    End
    else
    Begin

        -- Here I have just changed 7 -> 9 right chr. 
        SET @lastval = (SELECT MAX(CAST(RIGHT(enqid, 9) AS INT)) FROM ENQ$Header)

        -- I have changed the just below line, I used format function to achieved our desire requirement easily.
        SET @nextenqid = 'ENQ' + FORMAT(@lastval + 1, '000000000')
    End
    Return @nextenqid 
end