Generate running no. with prefix text in SQL

Below is the SQL script to generate the running no. along with prefix text.

USE Master

GO

Create table tblobj(Name varchar(20))
insert into tblobj(Name) values ('SampleName00001')
insert into tblobj(Name) values ('SampleName00002')

GO

Declare @Prefix varchar(20)
Set @Prefix = 'SampleName'
Declare @RunningNumber varchar(5)
Declare @count int
Select @count = count(1) from tblobj 
if @count = 0 
begin
	Select @RunningNumber = replicate('0',5 - len((right(@Prefix + '00000',5) + 1))) + cast((right(@Prefix + '00000',5) + 1) as varchar)
end
else
begin
	Select @RunningNumber = replicate('0',5 - len((right(max(Name),5) + 1))) + cast((Max(right(Name,5)) + 1) as varchar)
	From tblobj Where Name Like @Prefix + '%'
end
Select @Prefix + Cast(@RunningNumber as varchar)
Advertisements

#running-number-in-sql