Move cursor to the next row when condition satisfies

Question

I have written a cursor to iterate over rows of a table. My requirement is I dont want to check my condition for all the columns. if my condition matches with column 1, then don't check the condition for other columns and move the cursor to the next row. If condition doesn't match with column1 then check for column2 and so on. I have tried using the Break and continue both the keyword but none working as per my requirement. Below is my cursor.

Declare @InputResourceId int,@ResourceTypeId int,@CoreAppId int,@SiteId int,@CompetencyID int,@EstaffId int,
@CostCenterId int, @PVLocationId int,@EngineeringCostId int, @CostCategoryId int, @ExpenseTypeId int

Declare InputResourceCursor CURSOR FOR

SELECT InputResourceId, ResourceTypeId,CoreAppId, SiteId, CompetencyId, EStaffId, CostCenterId, PVLocationId
from InputResource where EFITID = 214

OPEN InputResourceCursor

FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId

WHILE (@@FETCH_STATUS = 0)

BEGIN
    
    Declare @Status1 int
    
    SET @Status1 = (Select Status from tblResourceType where ResourceTypeId = @ResourceTypeId)
    IF(@Status1 = 0)
        BEGIN
            UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
-- Here I want to move my cursor to the next row. If I put my Fetch next here then also its not working properly.
        END

    SET @Status1 = (Select Status from tblCoreApp where CoreAppId = @CoreAppId) 
    IF(@Status1 = 0)
        BEGIN
            UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        END

    SET @Status1 = (Select Status from tblSiteMaster where SiteId = @SiteId)    
    IF(@Status1 = 0)
        BEGIN
            UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        END

    SET @Status1 = (Select Status from tblCompetency where CompetencyId = @CompetencyID)    
    IF(@Status1 = 0)
        BEGIN
            UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        END

    SET @Status1 = (Select Status from Estaff where EStaffId = @EstaffId)   
    IF(@Status1 = 0)
        BEGIN
            UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        END

    SET @Status1 = (Select Status from CostCenter where CostCenterId = @CostCenterId)   
    IF(@Status1 = 0)
        BEGIN
            UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        END

    SET @Status1 = (Select Status from PlanViewLocationMaster where PlanviewLocationID = @PVLocationId) 
    IF(@Status1 = 0)
        BEGIN
            UPDATE InputResource SET Status = 2 where InputResourceId = @InputResourceId
        END

    FETCH NEXT FROM InputResourceCursor INTO @InputResourceId,@ResourceTypeId,@CoreAppId,@SiteId,@CompetencyID,@EstaffId,@CostCenterId,@PVLocationId

END

CLOSE InputResourceCursor
DEALLOCATE InputResourceCursor

Answer 1

I believe you can move all of your status selects inside a single EXISTS(select1 UNION ALL select2 UNION ALL ...) condition, where each selectN is of the form Select <anything> from ... where ... and Status = 0, similar to your original status lookups. If I understand the likely execution plan, the EXISTS() should shortcut the UNION ALL, so that once a select is found that satisfies its condition, the remaining selects will be skipped.

This approach would allow all of your logic to be be wrapped up into a single set operation, eliminating the cursor loop.

UPDATE I
SET Status = 2
FROM InputResource I
WHERE I.EFITID = 214
AND EXISTS (
        -- The selected "42 AS Foo" values below are ignored.
        -- We only care about EXISTS().
        Select 42 AS Foo from tblResourceType T where T.ResourceTypeId = I.ResourceTypeId And T.Status = 0
        UNION ALL
        Select 42 AS Foo from tblCoreApp T where T.CoreAppId = I.CoreAppId AND T.Status = 0
        UNION ALL
        Select 42 AS Foo from tblSiteMaster T where T.SiteId = I.SiteId AND T.Status = 0
        UNION ALL
        Select 42 AS Foo from tblCompetency T where T.CompetencyId = I.CompetencyID AND T.Status = 0  
        UNION ALL
        Select 42 AS Foo from Estaff T where T.EStaffId = I.EstaffId AND T.Status = 0
        UNION ALL
        Select 42 AS Foo from CostCenter T where T.CostCenterId = I.CostCenterId AND T.Status = 0 
        UNION ALL
        Select 42 AS Foo from PlanViewLocationMaster T where T.PlanviewLocationID = I.PVLocationId AND T.Status = 0
)

Another equivalent alternative is to use a CROSS APPLY instead of EXISTS(). A cross-apply is like an inner-join to a subselect. If no inner row is matched, the outer row is discarded. An extra subquery layer is needed to implement a TOP 1 limit to cause shortcutting of the inner selects once a match is found.

UPDATE I
SET Status = 2
FROM InputResource I
CROSS APPLY (
    SELECT TOP 1 *
    FROM (
        Select ...
   ) U
) C
WHERE I.EFITID = 214

In any case, I would encourage testing to ensure that the result has the intended performance and the shortcut logic is in place to avoid unnecessary subquery executions. (If you set up a scenario where the first or second subselect matches all, the execution plan should show zero rows accessed for the other selects.)

Lastly, double check that you have appropriate indexes on the tabled referenced in these subqueries. Most look like PK lookups, so you are likely good.