AutoGenrate Number in SQL.

Hi try this below code

 

declare @call varchar(64) =10 --Input parameter


BEGIN

DECLARE @length INT
DECLARE @result VARCHAR(128)

SET @result = ''
SET @length = 0

DECLARE @appendZero VARCHAR(5)

SET @appendZero = '000000'
SET @length = Len(ltrim(rtrim(@call)))

DECLARE @i INT

SET @i = 1

IF (@length > 0)
BEGIN
WHILE (@i != 5)
BEGIN
IF (@length = @i)
SET @result = Substring(@appendZero, 0, (6 - @i))
SET @i = @i + 1
END
END


SET @result = @result + @call

select 'UR-'+@result
END

Try below code:

 

'UR-' + RIGHT( '00000' + CONVERT(VARCHAR(250), [CallNo]), 5)

Thanks Manaus.

 

But if i want to add character then  like 'UR-00000' then what will be the code?

 

I tried 

RIGHT( 'UR-00000' + CONVERT(VARCHAR(250), [CallNo]), 7) 

but not success.

 

 Thanks

Basit