Creating series '22-47' '23-48' '24-49' in incremental order

Today I got a question in one of the SQL Forum, the problem statement was

“Hi, I have string like '21-46' i need output like '22-47' '23-48' '24-49'incremental order. etc...plz help me..”

This reminds me the Pl/SQL session we use to have in our college days and my dbms faculty, pretty Snehal Mam…

So here he is the answer for the same.

create proc sp_NoSeries

@howLong int,

@strNo varchar(50)

as

begin

 try

     declare @cntr int

     declare @no1 int

     declare @no2 int

    declare @msg varchar(100)

   

     set @howLong = 6

     set @cntr = 0

     while (@cntr<@howLong)

     begin

           print @strNo

           set @no1=cast(substring(@strNo,0,charindex('-',@strNo)) as int) + 1

           set @no2=cast(substring(@strNo,charindex('-',@strNo)+1,len(@strNo)) as int) + 1

          

           set @strNo=cast(@no1 as varchar(10)) +'-' + cast(@no2 as varchar(10))

          

           set @cntr = @cntr +1

     end

 end try

 begin catch

     print 'Error occur that is '

     set @msg = (select error_message())

     print @msg

 end catch

go

Lets execute the procedure.

exec sp_NoSeries 6,'21-46'

21-46

22-47

23-48

24-49

25-50

26-51

exec sp_NoSeries 6,'21-df46'

21-df46

Error occur that is

Conversion failed when converting the varchar value 'df46' to data type int.

To be happy always, be a coder, to learn happiness, love coder! J

 

Post Reference: Vikram Aristocratic Elfin Share