ErikIvanov

taskPlaner

Jul 27th, 2021
1,057
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE SEQUENCE dbo.seqPlanerCycle
  2. AS BIGINT
  3. START WITH 1
  4. MINVALUE 1
  5. MAXVALUE 20
  6. CYCLE
  7. CACHE
  8. GO
  9.  
  10. CREATE SEQUENCE dbo.seqPlanner
  11. AS BIGINT
  12. START WITH 1
  13. NO CYCLE
  14. NO CACHE
  15. GO
  16.  
  17. CREATE TABLE dbo.planner (
  18.   Nr BIGINT NOT NULL DEFAULT (NEXT VALUE FOR [seqPlanner])
  19.  ,AppId VARCHAR(256) NOT NULL
  20.  ,Task INT NOT NULL DEFAULT (1)
  21.  ,Date DATETIME NOT NULL DEFAULT (GETDATE())
  22.  ,CONSTRAINT PK_planner_Ident PRIMARY KEY CLUSTERED (AppId)
  23. )
  24. GO
  25.  
  26. CREATE PROCEDURE dbo.taskPlaner(@appId VARCHAR(256), @task INT OUTPUT)
  27. AS
  28. BEGIN
  29.   SET NOCOUNT ON
  30.  
  31.   DECLARE
  32.       @curTime DATETIME
  33.     , @id VARCHAR(256) = NULL
  34.     , @delay INT = 5
  35.     , @offset DATETIME
  36.     , @maxProcess INT
  37.     , @i INT = 0;
  38.  
  39.  
  40.   SET @curTime = GETDATE()
  41.   SET @offset = DATEADD(MINUTE, -@delay, @curTime)
  42.  
  43.   SELECT @maxProcess = CAST(maximum_value AS INT)
  44.   FROM sys.sequences
  45.   WHERE object_id = object_id ( 'dbo.seqPlanerCycle', 'SO' )
  46.  
  47.   WHILE @i < @maxProcess BEGIN
  48.     SELECT @task = NEXT VALUE FOR seqPlanerCycle
  49.     SELECT TOP(1) @id = AppId
  50.     FROM planner
  51.     WHERE Task = @task
  52.       AND
  53.       (
  54.         Date < @offset
  55.         OR AppId = @appId
  56.       )
  57.   END
  58.  
  59.   BEGIN TRANSACTION
  60.     IF @id IS NOT NULL BEGIN
  61.       IF @id != @appId AND @id IS NOT NULL
  62.         UPDATE planner SET
  63.           Task = NULL
  64.         WHERE AppId = @id
  65.  
  66.       UPDATE planner SET
  67.         Date = @curTime
  68.         , Task = @task
  69.       WHERE AppId = @appId
  70.      
  71.       IF @@rowcount = 0
  72.         THROW 60000, 'not found task', 1;
  73.     END ELSE BEGIN
  74.         INSERT INTO planner (AppId, Task, Date)
  75.         VALUES (@id, @task, @curTime)
  76.     END
  77.   COMMIT TRANSACTION
  78. END
  79. GO
RAW Paste Data