Auto-calculated field which includes letters and numbers I'm currently creating a workflow form to allow a new part creation, as a part of this I need to write an SQL statement which must be a stored procedure to auto populate the Part code field. I have done this previously for both customer and supplier using the following method:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Udef_Wff_SUPCR_AutoSuppCode_SP]
    @FormID                 Int
    ,@UserID                VarChar(5)
As
Declare 
    @SPC_IntSupCode         Int
    ,@INS_IntSupCode            Int


Select @INS_IntSupCode = MAX(SUCODE)
From PL_ACCOUNTS Where ISNUMERIC(SUCODE) = 1

Select @SPC_IntSupCode = MAX(SUCODE) 
FROM SPECIALLive.dbo.PL_ACCOUNTS WHERE ISNUMERIC(SUCODE) = 1

If @INS_IntSupCode &gt;= @SPC_IntSupCode
Begin
    Select Convert(VarChar(10), (@INS_IntSupCode+1))
End
If @INS_IntSupCode &lt;= @SPC_IntSupCode
Begin
    Select Convert(VarChar(10), (@SPC_IntSupCode+1))
End


For both supplier and customer the data is identical in 2 databases and also the ID field is numeric. However my part ID's are in the format of

M10000001
M10000002
M10000003


and I will be updating the databases individually as the data contained will be different. As the database already contains entries I will need to locate the last entry and add 1 to the numeric part of the code. This is what I have come up with so far:

USE [INSULATIONLive]
GO
/****** Object:  StoredProcedure [dbo].[Udef_Wff_ParCR_AutoPartCode_SP]Script Date: 07/06/2016 09:06:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Udef_Wff_ParCR_AutoPartCode_SP]
    @FormID                 Int
    ,@UserID                VarChar(5)
As
Declare 
    @I          Int
    ,@PC        Varchar(16)

select @PC = Max(PMA_PART_CODE)
From [dbo].[ASC_PMA_TBL]
set @I = right(@PC,7)

Begin
select convert (varchar(16), @I +1,7)
end


No idea if it will work yet, any helpful comments much appreciated or alternative methods of achieving this
Don't do the SELECT MAX thing; you can run into concurrency problems there. If the patterns are simple enough, and have only a single increasing numeric portion, you can probably get away with using an identity column plus a persisted computed column to create the formatted string value:

CREATE TABLE IDTest (
    row_id int IDENTITY(1,1) NOT NULL,
    part_id AS 'M' + REPLICATE('0', 10 - LEN(CAST(row_id AS varchar(10)))) + CAST(row_id AS varchar(10)) PERSISTED,
    part_name varchar(50) NOT NULL
)

INSERT INTO IDTest (part_name) VALUES ('First part'), ('Second part'), ('Third part')

SELECT * FROM IDTest


If your patterns need to get fancier than that (multiple numeric segments, variations in the letters, etc.) then take a look at using sequences and triggers.