Getting the Next Number in a Custom Number Format

This sql script is very useful for getting the next number in a numeric order. Sometimes you'll have a requirement to have an incrementing number that has a special format. For example, your invoice number may be 2018-0001 (4 Digit year, dash, incrementing number starting at 1). There are a few ways this can be done, but if you want or need to do it one field, getting the number to increment can be a challenge. This sample script will show you how to accomplish this.

Create View viewNextNumber
AS

Select Max(Convert(Int,Right(YourNumberField,4))) AS NextID,
	Convert(Int,Left(YourNumberField,4)) AS InvoiceYear
From tblARInvoices
Where ISNUMERIC(Right(YourNumberField,4)) = 1
	And Convert(Int,Left(YourNumberField,4)) > 1900
Group By Convert(Int,Left(YourNumberField,4))
GO

Create Procedure uspGetNextID
	@prmYear Int --Pass in the Year you want to get the next ID For
AS
	
Declare @prmNextID Int
Select @prmNextID = NextID
From viewNextNumber
Where InvoiceYear = @prmYear

Select Convert(varchar(4),@prmYear) + Right('0000' + Convert(Varchar(4),IsNull(@prmNextID,0) + 1),4) AS NextID
GO

A sample call to this procedure would be:
Exec uspGetNextID 2018
which would return: 20180001