SQL check if row exists in cursor

Question

I am doing a select that returns a table of roleIds. I was looking for a method of iterating through the values and run a delete or (stored proc) for each row value. After trying many different suggestions this is what I have:

BEGIN TRAN
DECLARE @RoleId bigint

DECLARE cur CURSOR FOR SELECT DISTINCT cr.RoleId
FROM ClientRole AS cr
INNER JOIN userRole AS ur ON cr.RoleId = ur.RoleId
AND cr.ClientId = 564564564

OPEN cur

FETCH NEXT FROM cur INTO @RoleId

WHILE @@FETCH_STATUS = 0 BEGIN

        --NEED TO CHECK IF RolePermissions ACTUALLY HAS THE PERMISSION
        DELETE  
        FROM RolePermissions
        WHERE RoleId = @RoleId
        AND PermissionId = 12341234

    FETCH NEXT FROM cur INTO @RoleId
END

CLOSE cur    
DEALLOCATE cur

ROLLBACK

Currently when trying to "delete" is gives an error because the row it is trying to delete doesn't exists for some RoleIds. I tried If, WHILE, CASE but not sure what I should be doing here as I don't know how to pass over the RoleId if it doesnt have the row. It seems cursors arent recommended but could not make the same work by putting it in a temp table.

Appreciate any suggestions

EDIT::

Initially I was trying to run a stored proc from the cursor: Originally I was trying to run a stored proc on each roleId:

    begin tran
    exec dbo.usp_DeleteRolePermission @RoleId = @RoleId,
    @PermissionId =  12341234,
    @PublishToCache = 1
commit

Answer 1

You probably do not need a cursor for this. A single statement like this can easily be transformed into a procedure:

declare @ClientId     int = 564564564;
declare @PermissionId int = 12341234;

delete rp
from RolePermissions rp
where PermissionId = @PermissionId
  and exists (
    select 1
    from ClientRole as cr
      inner join userRole as ur 
        on cr.RoleId = ur.RoleId
      and cr.ClientId = @ClientId
      where ur.RoleId = rp.RoleId
      );