Auto Increment with TEX

I have a table name: my_table

Column: ID, name, department (department is a drop down list as below:

AOD
CRM
RMD

Now I want to that when auto increment will generated it will depend on department as like below.

If department AOD then ID will generated as below:

AOD00001
AOD00002
AOD00003

If department CRM then ID will generated as below:

CRM00001
CRM00002
CRM00003

If department RMD then ID will generated as below:

RMD00001
RMD00002
RMD00003

or

AOD00001
CRM00002
AOD00003
RMD00004
CRM00005

I'd suggest either to use an additional table or by adding a new counter field to your current Departments table.

Then build an stored procedure or use a BEFORE INSERT trigger, to quietly add the next ID every time a new record is inserted.

CREATE TABLE [dbo].[DepartmentSeq]
(
    [department]        varchar(10)     NOT NULL,
    [sequence]          int             NOT NULL,

    CONSTRAINT [PK_DepartmentSeq] 
        PRIMARY KEY CLUSTERED ([department] ASC)
) 
ON [PRIMARY];
GO

--= SP [sp_DeparmentSeq_next]
--------------------------------------------------
CREATE PROCEDURE [dbo].[sp_DeparmentSeq_next]
(
@department varchar(10),
@next_sequence int OUT
)
AS
BEGIN

    DECLARE @sequence int;
    SET @sequence= 0;
    SET @next_sequence = 0;

    BEGIN TRY

        SELECT   @sequence = [sequence]
        FROM     [dbo].[DepartmentSeq] WITH (UPDLOCK)
        WHERE    [department] = @department;

        IF @@ROWCOUNT > 0
        BEGIN 
            SET @sequence= @sequence + 1;

            UPDATE [dbo].[DepartmentSeq]
            SET    sequence = @sequence
            WHERE  [department] = @department;
        END
        ELSE
        BEGIN
            SET @sequence = 1;

            INSERT INTO [dbo].[DepartmentSeq] (department, sequence)
            VALUES      (@department, @sequence);
        END

        SET @next_sequence = @sequence;

    END TRY
    BEGIN CATCH

        DECLARE @en INT, @es INT, @ep SYSNAME, @el INT, @em NVARCHAR(2048);
        SET     @en = ERROR_NUMBER();
        SET     @es = ERROR_SEVERITY();
        SET     @ep = ERROR_PROCEDURE()
        SET     @el = ERROR_LINE();
        SET     @em = ERROR_MESSAGE();
        -- EXEC [dbo].[vsp_log_errors_insert] @ep, @en, @em, @es, @el;
        RAISERROR(@em, @es, @el);
        RETURN @en;

    END CATCH

    RETURN 0;

END
GO

As a second option, if there is a limited number of departments and depending on which version of SQL Server you use, you can use a SEQUENCE for each department.

StackExchange Database Administrators Q#174524, answer score: 3