I love functions... below is the function declaration but first the insert statment

INSERT INTO dbo.Invoice ( Invoice_id, Customer_name, Invoice_amount )

VALUES ( dbo.fn_NextKey(05), 'ACME Corp.', 12345.67 )

This technique also works with SELECT inserts for multi-row inserting joy. The function is also easy to modify for different key compositions mmyy-nnnnnnnnnn or what ever...

Cheers!

CREATE FUNCTION dbo.fn_NextKey( @year int )

RETURNS varchar(10)

AS

BEGIN

/* ASSERTIONS

* Invoice_Id has a fixed pattern of yy-nnnnnn where yy is last two digits of a year

* and nnnnnn is a 6 digit 0 padded number that is incremented by 1

*/

DECLARE @pattern char(3), @answer varchar(10)

SET @pattern = right('00'+convert(varchar(10),@year),2) + '-'

SELECT

@answer = CASE WHEN max(Invoice_id) IS NULL

THEN @pattern + '000001' /* first invoice of given year */

ELSE @pattern + right('000000'+convert(varchar(10),convert(int,substring(max(Invoice_id),4,6))+1),6)

END

FROM dbo.Invoice

WHERE Invoice_id LIKE @pattern+'%'

RETURN @answer

END