taskPlaner

CREATE SEQUENCE dbo.seqPlanerCycle
AS BIGINT
START WITH 1
MINVALUE 1
MAXVALUE 20
CYCLE
CACHE
GO

CREATE SEQUENCE dbo.seqPlanner
AS BIGINT
START WITH 1
NO CYCLE
NO CACHE
GO

CREATE TABLE dbo.planner (
  Nr BIGINT NOT NULL DEFAULT (NEXT VALUE FOR [seqPlanner])
 ,AppId VARCHAR(256) NOT NULL
 ,Task INT NOT NULL DEFAULT (1)
 ,Date DATETIME NOT NULL DEFAULT (GETDATE())
 ,CONSTRAINT PK_planner_Ident PRIMARY KEY CLUSTERED (AppId)
)
GO

CREATE PROCEDURE dbo.taskPlaner(@appId VARCHAR(256), @task INT OUTPUT)
AS
BEGIN
  SET NOCOUNT ON

  DECLARE
      @curTime DATETIME
    , @id VARCHAR(256) = NULL
    , @delay INT = 5
    , @offset DATETIME
    , @maxProcess INT
    , @i INT = 0;


  SET @curTime = GETDATE()
  SET @offset = DATEADD(MINUTE, -@delay, @curTime)

  SELECT @maxProcess = CAST(maximum_value AS INT)
  FROM sys.sequences
  WHERE object_id = object_id ( 'dbo.seqPlanerCycle', 'SO' )

  WHILE @i < @maxProcess BEGIN
    SELECT @task = NEXT VALUE FOR seqPlanerCycle
    SELECT TOP(1) @id = AppId
    FROM planner
    WHERE Task = @task
      AND
      (
        Date < @offset
        OR AppId = @appId
      )
  END

  BEGIN TRANSACTION
    IF @id IS NOT NULL BEGIN
      IF @id != @appId AND @id IS NOT NULL
        UPDATE planner SET
          Task = NULL
        WHERE AppId = @id

      UPDATE planner SET
        Date = @curTime
        , Task = @task
      WHERE AppId = @appId

      IF @@rowcount = 0
        THROW 60000, 'not found task', 1;
    END ELSE BEGIN
        INSERT INTO planner (AppId, Task, Date)
        VALUES (@id, @task, @curTime)
    END
  COMMIT TRANSACTION
END
GO