--Probably easiest would be to put the control values into a separate table, and if necessary use exclusive locks on that table when seeding that table.

CREATE TABLE dbo.control_table (

prefix char(1) NOT NULL,

suffix char(6) NOT NULL,

control_value AS CAST(prefix + suffix AS char(7)),

CONSTRAINT control_table__PK PRIMARY KEY ( prefix )

)

------------------------------------------------------------------------------------------

SET NOCOUNT ON

--after preparing the incrementing code,

--do an initial load of the current highest values into the control_table

/*

INSERT INTO dbo.control_table

SELECT 'A', SUBSTRING(alphanumeric, 2, 6)

FROM dbo.your_data_table --WITH (TABLOCKX) --if needed to insure you get the highest value

WHERE alphanumeric LIKE 'A%'

INSERT INTO dbo.control_table

SELECT 'C', SUBSTRING(alphanumeric, 2, 6)

FROM dbo.your_data_table --WITH (TABLOCKX) --if needed to insure you get the highest value

WHERE alphanumeric LIKE 'C%'

*/

INSERT INTO dbo.control_table VALUES('C', '004658')

------------------------------------------------------------------------------------------

--code to increment a given prefix

DECLARE @prefix_to_increment char(1) --set to 'A' or 'C'

SET @prefix_to_increment = 'C'

DECLARE @new_value TABLE (

prefix char(1) NOT NULL,

suffix char(6) NOT NULL

)

UPDATE dbo.control_table WITH (TABLOCKX) --insure only one value assigned at a time

SET suffix = RIGHT('000000' + CAST(suffix + 1 AS varchar(6)), 6)

OUTPUT

INSERTED.prefix, INSERTED.suffix INTO @new_value

WHERE

prefix = @prefix_to_increment

SELECT TOP (1) prefix + suffix AS new_value

FROM @new_value

DROP TABLE dbo.control_table --for testing only, of course

Edit: Added code tags.