Microsoft SQL Sample Code

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

If you need any help implementing this function or modifications made for your needs, please Click Here to get a free quote for your Microsoft SQL Development needs. 

Click Here to learn more about the WSI quoting process.