Incrementing a Non Numeric Index in SQL Server

So I have this client, and all his sales invoices in the old system are either numeric, or they are numeric (1000) with a single character prefix (C1000).   We want to auto increment the index automatically, but the autoincrement stuff is not going to work here.



So I wrote a function based on this SQL.

select 'C' + isnull(cast(1+max( cast(SUBSTRING(Invoice_no,2,99) as int)) as varchar),'1000')
from Sales where Invoice_No like 'C%'

Let's work from the inside out.

1. Invoice_no is the column to increment.  ('C1234')


2. first we substring the first character off with SUBSTRING(Invoice_no,2,99) ('1234')


3. we use cast to find the integer of it (1234)


4. we use 1+max to aggregate (find the max value of this integer) and add one. (1235)


5. we cast the result back to a varchar ('1235')


6. then - if isnull gives us a nulll, we use the hard-coded value of '1000'


7. we prepend the 'C' back on  ('C1235')

Now we make this into a function so we can use it like "GetDate()" in the default value of the column.

ALTER FUNCTION [dbo].[NextSalesID] ()

RETURNS varchar(10)
AS
BEGIN
    DECLARE @Answer varchar(10)   
    select @Answer= 'C' + isnull(cast(1+max( cast(SUBSTRING(Invoice_no,2,99) as int)) as varchar),'1000')
    from Sales where Invoice_No like 'C%'
    RETURN @Answer
END

 Now we have to add it as the default value for our column.  This is accomplished by editing the table in SQL Server Management Studio, selecting the column, and...

Binding a scalar Function to a Column Default

MAKE SURE that management studio doesn't add quotes '' around your function name.

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.