USP_ACTIONITEMRESTRICTION_DELETE_BY_ACTIONITEM

create procedure dbo.USP_ACTIONITEMRESTRICTION_DELETE_BY_ACTIONITEM
(
    @ACTIONITEMID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    DECLARE @ACTIONITEM table(id_num int IDENTITY(1,1),restrictionid uniqueidentifier)
    DECLARE @i int
    DECLARE @total int
    DECLARE @ID uniqueidentifier

    if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    INSERT INTO @ACTIONITEM(restrictionid)
    SELECT ID from dbo.ACTIONITEM_RESTRICTION (NOLOCK)
        where ACTIONITEMID = @ACTIONITEMID

    SET @i = 1
    SELECT @total = count(*) from @ACTIONITEM

    WHILE (@i <= @total)
    BEGIN
        SET @ID = NULL
        SELECT @ID = restrictionid FROM @ACTIONITEM where id_num = @i

        EXEC USP_ACTIONITEMRESTRICTION_DELETE @ID = @ID, @CHANGEAGENTID = @CHANGEAGENTID

        SET @i = @i + 1

    END
end