Change SQL result set before returning from stored procedure

Question

I have a simple table in my SQL Server 2008 database:

Tasks_Table
-id
-task_complete
-task_active
-column_1
-..
-column_N

The table stores instructions for incomplete tasks that must be performed by the service.

I want to be able to scale my system in the future. So far, only 1 service on one computer is read from the table. I have a stored procedure that selects all unfinished and inactive tasks. When the service starts processing tasks, it updates the task_active flag in all returned rows.

To enable system scaling, I want to enable service deployment on other machines. Since I want the task to not return to more than one service, I have to update the stored procedure, which returns incomplete and inactive tasks.

, ( 1 - , ) task_active .

, , SELECT ?

Answer 1

dequeue, OUTPUT MSDN, . Queues OUTPUT (Transact- SQL):

UPDATE TOP(1) Tasks_Table WITH (ROWLOCK, READPAST)
 SET task_active = 1
 OUTPUT INSERTED.id,INSERTED.column_1, ...,INSERTED.column_N 
 WHERE task_active = 0;

ROWLOCK, READPAST : / mutliple threads/process dequeue. .

, CTE:

WITH cte AS (
  SELECT TOP(1) id, task_active, column_1, ..., column_N
  FROM Task_Table WITH (ROWLOCK, READPAST)
  WHERE task_active = 0
  ORDER BY <order by criteria>)
UPDATE cte
  SET task_active = 1
OUTPUT INSERTED.id, INSERTED.column_1, ..., INSERTED.column_N;

enqueue/dequeue .