New Id Genrate From Database Query

 begin

   declare @rcode as int,@App_No_New AS NVARCHAR(12),@ID_New BIGINT

select @ID_New=isnull(max(Code),0)  from Waqf_TimeExtension_Main 

set @ID_New=(@ID_New)+1

select @ID_New

IF @ID_New<=9

BEGIN

SET @App_No_New='00000' + CAST(@ID_New AS NVARCHAR) 

END

ELSE IF @ID_New>9 AND @ID_New<=99

BEGIN

SET @App_No_New='0000' + CAST(@ID_New AS NVARCHAR)

END

ELSE IF @ID_New>99 AND @ID_New<=999

BEGIN

SET @App_No_New='000' + CAST(@ID_New AS NVARCHAR)

END

ELSE IF @ID_New>999 AND @ID_New<=9999

BEGIN

SET @App_No_New='0' + CAST(@ID_New AS NVARCHAR)

END

ELSE IF @ID_New>9999 

BEGIN

SET @App_No_New=@ID_New

END

SET @App_No_New='TE' + CAST(DATEPART(YEAR,GETDATE()) AS  NVARCHAR) + @App_No_New

select @App_No_New

end