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
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.