CREATE PROCEDURE [dbo].[sp_ProcessBatch]
@BatchSize int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @ProcessorState bit
set @ProcessorState = 1
UPDATE NotificationMeta
SET NotificationMeta.StateID = 2, -- UPDATE STATE AS PART OF SELECT AND OUTPUT
NotificationMeta.TimePicked = GETUTCDATE(),
RetryCount = RetryCount + 1
OUTPUT NotificationMeta2.ID, NotificationMeta2.EchoToken -- RETURN PICKED ROWS
FROM NotificationMeta
JOIN
(SELECT TOP (@BatchSize) nd.* FROM NotificationMeta
WITH (UPDLOCK,READPAST) -- TO ENSURE NO MULTIPLE UPDATES TO SAME ROW
JOIN NotificationData nd ON ID = NotificationMeta.NotificationID
WHERE StateID = 1 AND GETUTCDATE() < ExpiryTime
ORDER BY nd.EntryTime ASC, Priority ASC) AS NotificationMeta2
ON NotificationMeta.NotificationID = NotificationMeta2.ID
END
No comments:
Post a Comment