--this routine take the input number and increments it by 1
--Document numbers commonly are in the format of a few alpha characters followed by a number
--like 'ORD0001' or 'PO0001'
--this routine will strip off the trailing numbers, increment, and then put the two parts
--back together again
IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_GetNextNumber') begin
DROP proc dd_GetNextNumber
end
GO
CREATE proc dd_GetNextNumber
-- dd_GetNextNumber ''
-- dd_GetNextNumber '12'
-- dd_GetNextNumber 'b12123cde00123'
@InputNumber varchar(15)
AS
set transaction isolation level read uncommitted
--validate the input
if @InputNumber = '' begin
select '' as NewNumber
return
end
--declare some variables
declare @a varchar(1)
declare @i int
declare @front varchar(15)
declare @back varchar(15)
declare @intback int
--get the length of the input
set @i = len(@InputNumber)
--prime our variable for the loop with the last character in the input
set @a = substring(@InputNumber,@i,1)
--start at the end of the number, walk back until we find a non-numeric character
while 1= 1 begin
if not ISNUMERIC(@a) = 1 begin
break
end
set @i = @i-1
if @i = 0 begin
break
end
set @a = substring(@InputNumber,@i,1)
end
--get the front part
set @front = SUBSTRING(@InputNumber,1,@i)
--get the back part, increment
--use a varchar type so we can get the length and not lose any leading zeros
set @back = SUBSTRING(@InputNumber,@i+1,len(@InputNumber)-@i)
set @intback = @back + 1
--add it all back together
select @front + right('000000000000000' + convert(varchar(15),@intback),len(@InputNumber)- @i) as NewNumber
GO
GRANT EXEC ON dd_GetNextNumber TO PUBLIC