CODEWITHSUNDEEP
sql-serverstored-procedures
Stpete111
Stpete111

Reputation: 3457

Modify SQL Server stored procedure to check for condition before executing Update statement

Azure SQL Server - I have an inherited stored procedure which runs asynchronously, triggered by an Azure Service Fabric service which runs infinitely:

PROCEDURE Sources.ForIndexing
    (@SourceId BIGINT)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @BatchId uniqueidentifier

    SELECT @BatchId = CaptureBatch
    FROM [Data].[Raw]
    WHERE CaptureId = (SELECT MAX(CaptureId)
                       FROM [Data].[Raw]
                       WHERE SourceId = @SourceId)

    UPDATE [Data].[Raw]
    SET [Status] = 501
    WHERE SourceId = @SourceId
      AND CaptureBatch = @BatchId
END
GO

In this Data.Raw table CaptureId is the Primary Key and is auto-incrementing. The records in this table are grouped by SourceId and CaptureBatch. One SourceId can have several CaptureBatch's. The first part of this procedure finds the latest CaptureBatch group by looking at the MAX CaptureId of a given SourceId. The UPDATE statement then sets the Status column of those records to 501.

What I need to do is add a condition to the stored procedure where, after the SELECT statement runs, says if the Status column of any given record over which this procedure iterates has a value of 1, do not execute the UPDATE statement on that record.

I thought it might be as simple as modifying the SELECT part to say:

WHERE CaptureId = (SELECT MAX(CaptureId)
                   FROM [Data].[Raw]
                   WHERE SourceId = @SourceId
                     AND Status <> 1)

But I believe that's only going to select a Status that's not 1 for that one record which contains the MAX CaptureId, correct? I may be overthinking this, but it seems I need some kind of IF statement added to this.

Upvotes: 0

Views: 314

Answers (2)

IVNSTN
IVNSTN

Reputation: 9324

SELECT TOP (1)
   @BatchId = r.CaptureBatch
FROM [Data].[Raw] r
WHERE r.SourceId = @SourceId
ORDER BY r.CaptureId DESC

UPDATE r SET 
   [Status] = 501
FROM [Data].[Raw] r
WHERE r.SourceId = @SourceId
  AND r.CaptureBatch = @BatchId
  AND r.Status <> 1

Upvotes: 1

Vasya
Vasya

Reputation: 103

IF (SELECT count(CaptureId)
                       FROM [Data].[Raw]
                       WHERE SourceId = @SourceId and Status = 1) > 0 BEGIN
 UPDATE [Data].[Raw]
    SET [Status] = 501
    WHERE SourceId = @SourceId
      AND CaptureBatch = @BatchId
END

Upvotes: 0

Related Questions