I was wondering if anyone has some working code or ideas to auto generate an item master code record. I have to do this for a client and I am not really sure how this might work.
I create a sql table as follows:
CREATE TABLE [dbo].[AC_TBLIDX](
[TblName] [varchar](10) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
[Idx] [int] NOT NULL
) ON [PRIMARY]
Then I use a stored procedure to populate the value the first time, increment each successive time and return the updated.
I call this stored procedure thru code so that I can handle any user errors.
In my opinion this works better than a max function on a field within the oitm table.
You can also add extra fields that store a prefix or suffix.
snippet from stored procedure
if exists(select Idx from [dbo].[AC_TBLIDX] where TblName = @Tbl)
set Idx = Idx + 1
where TblName = @Tbl
insert into [dbo].[AC_TBLIDX] (TblName, Idx)
values (@Tbl, 0)
set @return_value = (select Idx from [dbo].[AC_TBLIDX] where TblName = @Tbl)