1

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 >= @SPC_IntSupCode
Begin
    Select Convert(VarChar(10), (@INS_IntSupCode+1))
End
If @INS_IntSupCode <= @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

1

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.

  • I already have a database with 800+ parts already on it, will this way work? – Tania Jun 7 '16 at 12:28
  • Also am I understanding correctly that the above is creating a separate table and then populating the current table? – Tania Jun 7 '16 at 12:30
  • @Tania, yes, this would be for a newly created database. If it's an existing table that's already being populated this way, and you're just trying to fetch values, then this wouldn't help you. – db2 Jun 7 '16 at 12:46
  • yes I have a table which is already populated, I am creating the stored procedure for future part creation via a work flow form so that the part code is already populated on opening of the form. Thanks for trying – Tania Jun 7 '16 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.