Reset the ID counter on a stored procedure in SQL Server

Question

I'm developing a system that manages work orders for vehicles. The ID of work orders is composed as follows: OT-001-16.

Where OT- is a string, 001 is the counter, followed by - character and finally the number 16 is the current year.

Example:

If the current year is 2018, the ID should be OT-001-18.

The problem is when the year changes, the counter must restart from 001. I have a stored procedure to do that, but i think i'm doing a lot more work.

This is my stored procedure code:

CREATE PROCEDURE ot (@name varchar(100), @area varchar(100), @idate varchar(100), @edate varchar(100))
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @aux varchar(100);
  DECLARE @aux2 varchar(100);
  DECLARE @aux3 int;
  DECLARE @aux4 varchar(100);

  SELECT @aux = id_workorder FROM idaux;

  IF (@aux IS NULL)
    SET @aux = CONCAT('OT-000-', RIGHT(YEAR(GETDATE()), 2));

  SET
    @aux2 = SUBSTRING(
    @aux, CHARINDEX('-', @aux) + 1,
    LEN(@aux) - CHARINDEX('-', @aux) - CHARINDEX('-', REVERSE(@aux)));

  SET @aux3 = CAST(@aux2 AS int) + 1;

  SET @aux4 = @aux3;

  IF @aux3 < 1000
    IF @aux3 >= 10
      SET @aux4 = CONCAT('0', @aux4);
    ELSE
      SET @aux4 = CONCAT('00', @aux4);
  ELSE
    SET @aux4 = @aux4;

  DECLARE @f varchar(100);
  DECLARE @y varchar(50);

  SELECT TOP 1
    @y = id_workorder
  FROM workorder
  WHERE (RIGHT(id_workorder, 2)) = (RIGHT(YEAR(GETDATE()), 2))
  ORDER BY id_workorder DESC;

  DECLARE @yy varchar(10);

  SET
    @yy = RIGHT(@y, 2);
  DECLARE @yn varchar(10);

  SET
    @yn = RIGHT(YEAR(GETDATE()), 2);

  BEGIN
    IF @yn = @yy
    BEGIN
      DECLARE @laux varchar(20)
      SET @f = 'OT-' + @aux4 + '-' + RIGHT(YEAR(GETDATE()), 2);
      INSERT INTO workorder (id_workorder, name, area, initial_date, end_date)
        VALUES (@f, @name, @area, @idate, @edate);

      SELECT
        @laux = id_workorder
      FROM idaux

      IF (@laux IS NULL)
      BEGIN
        INSERT idaux (id_workorder) VALUES (@f);
      END
      ELSE
      BEGIN
        UPDATE idaux SET id_workorder = @f;
      END
    END
    ELSE
    BEGIN
      SET @f = CONCAT('OT-001-', (RIGHT(YEAR(GETDATE()), 2)));
      INSERT INTO workorder (id_workorder, name, area, initial_date, end_date)
        VALUES (@f, @name, @area, @idate, @edate);

      SELECT @laux = id_workorder FROM idaux;

      IF (@laux IS NULL)
      BEGIN
        INSERT idaux (id_workorder) VALUES (@f);
      END
      ELSE
      BEGIN
        UPDATE idaux SET id_workorder = @f;
      END
    END
  END

END

Basically, i created an auxiliar table to save the last Work Order ID, then from this table called idaux i take the ID and i compared to new possible ID by a string handling. Then if the year of the last ID saved are equal to the current year the counter increases, but if not the counter is restarted to 001, the new ID is updated in the auxiliar table and the Work Order is inserted to the table workorder.

My stored procedure works, but i need your help to optimize the stored procedure. Any question post on comments.

Answer 1

Here is how I'd setup the stored procedure and the underlying table to keep track of your work orders:

create database tmpWorkOrders;
go

use tmpWorkOrders;
go

/*
    The work order ID (as you wish to see it) and the
    work order counter (per year) will be separated into 
    two separate columns (with a unique constraint).
    The work order ID (you wish to see) is automatically
    generated for you and stored "persisted":
    http://stackoverflow.com/questions/916068/sql-server-2005-computed-column-is-persisted
*/
create table WorkOrders
    (
        SurrogateKey         int identity(1, 1) primary key not null,
        WorkOrderYear        int not null,
        WorkOrderCounter     int not null,
        WorkOrderID as
            N'OT-' + right(N'000' + cast(WorkOrderCounter as nvarchar), 3)
            + N'-' + right(cast(WorkOrderYear as nvarchar), 2)persisted,
        WorkOrderDescription nvarchar(200),
        constraint UQ_WorkOrderIDs
            unique nonclustered (WorkOrderYear, WorkOrderCounter)
    );
go

---

create procedure newWorkOrder
    (@WorkOrderYear int = null,
     @WorkOderCounter int = null,
     @WorkOrderDescription nvarchar(200) = null
    )
as
    begin
        /*
            If no year is given the the current year is assumed
        */
        if @WorkOrderYear is null
            begin
                set @WorkOrderYear = year(current_timestamp);
            end;
        /*
            If no work order counter (for the above year) is given
            then the next available one will be given
        */
        if @WorkOderCounter is null
            begin
                set @WorkOderCounter
                    = isnull(
                          (
                              select max(WorkOrderCounter)
                              from WorkOrders
                              where WorkOrderYear = @WorkOrderYear
                          ) + 1,
                          0
                            );
            end;
        else
        /*
                If a work order counter has been passed to the
                stored procedure then it must be validated first
            */
            begin
                /*
                    Does the work order counter (for the given year)
                    already exist?
                */
                if exists
                    (
                        select 1
                        from dbo.WorkOrders as wo
                        where wo.WorkOrderYear = @WorkOrderYear
                              and wo.WorkOrderCounter = @WorkOderCounter
                    )
                    begin
                        /*
                            If the given work order counter already exists
                            then the next available one should be assigned.
                        */
                        while exists
                            (
                                select 1
                                from dbo.WorkOrders as wo
                                where wo.WorkOrderYear = @WorkOrderYear
                                      and wo.WorkOrderCounter = @WorkOderCounter
                            )
                            begin
                                set @WorkOderCounter = @WorkOderCounter + 1;
                            end;
                    end;
            end;
        /*
            The actual insert of the new work order ID
        */
        insert into dbo.WorkOrders
            (
                WorkOrderYear,
                WorkOrderCounter,
                WorkOrderDescription
            )
        values
            (@WorkOrderYear,
             @WorkOderCounter,
             @WorkOrderDescription
            );
    end;
go

---

/*
    Some test runs with the new table and stored procedure...
*/

exec dbo.newWorkOrder @WorkOrderYear = null,
                      @WorkOderCounter = null,
                      @WorkOrderDescription = null;

exec dbo.newWorkOrder @WorkOrderYear = null,
                      @WorkOderCounter = 3,
                      @WorkOrderDescription = null;

exec dbo.newWorkOrder @WorkOrderYear = null,
                      @WorkOderCounter = 0,
                      @WorkOrderDescription = null;

exec dbo.newWorkOrder @WorkOrderYear = null,
                      @WorkOderCounter = 0,
                      @WorkOrderDescription = null;

exec dbo.newWorkOrder @WorkOrderYear = null,
                      @WorkOderCounter = 0,
                      @WorkOrderDescription = null;

/*
    ...reviewing the result of the above.
*/

select *
from dbo.WorkOrders as wo;

Note, that the "next available" work order counter is once given (1) as the maximum + 1 and once (2) increased until it does not violate the unique key constraint on the table anymore. Like this you have two different possibilities to go about it.

Answer 2

There are a number of observations based on your code that you could alter to optimize and guarantee your results.

I am not aware of your Table Structure, but it seems you are using natural keys for your IDs.

• Instead, use a surrogate key, such as INT/BIGINT to not only add efficiency in your table joins (no strings required), but potentially add another layer of security in your current design.
• Alternatively, normalize the column into the flags they are. For example: OT-001-05 has three elements: OT is a type of work order, 001 is the ID, and 15 is the year. Presently, OT determines the ID which determines the year.

SELECT @aux = id_workorder FROM idaux;

• idaux was not described. Is it a single value? If tabular, guarantee the result or it might break in the future.
• Even if you add MAX(id_workorder), your result will not work as you think. Since this is a VARCHAR, the greatest value of the leftmost character not tied will return.

@aux, CHARINDEX('-', @aux) + 1, LEN(@aux) - CHARINDEX('-', @aux) - CHARINDEX('-', REVERSE(@aux)));

• This is fine, but overall you could make the code clearer and easier to debug by splitting all three of those elements into their own variable. Your still using your method, but simplified a little (personally, CHARINDEX can be a pain).

  SET @aux = @Type -- 'OT' SET @aux2 = @ID -- The result of your previous code SET @aux3 = @Year -- your YY from GETDATE() -- then join SET @Work_Order = CONCAT(@aux, '-', @aux2, '-', @aux3)

Update: Currently, your column in idaux has the ID in the MIDDLE of your column. This will produce disastrous results since any comparison of IDs will happen in the middle of the column. This means at best you might get away with PATINDEX but are still performing a table scan on the table. No index (save for FULLTEXT) will be utilized much less optimized.

I should add, if you put the ID element into its own column, you might find using BINARY collations on the column will improve its performance. Note I have not tested attempting a BINARY collation on a mixed column