USP_ACTIONITEMTARGET_DELETE_BY_ACTIONITEM

CREATE procedure dbo.USP_ACTIONITEMTARGET_DELETE_BY_ACTIONITEM
(
    @ACTIONITEMID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @ADVOCACYGROUPID smallint = null
)
as
begin
    DECLARE @ACTIONITEM table(id_num int IDENTITY(1,1),groupid uniqueidentifier)
    DECLARE @i int
    DECLARE @total int
    DECLARE @ID uniqueidentifier

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

    INSERT INTO @ACTIONITEM(groupid)
    SELECT ID from dbo.ACTIONITEM_TARGET (NOLOCK)
        where ACTIONITEMID = @ACTIONITEMID
        and ADVOCACYGROUPID = ISNULL(@ADVOCACYGROUPID,ADVOCACYGROUPID)

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

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

        EXEC USP_ACTIONITEMTARGET_DELETE @ID = @ID, @CHANGEAGENTID = @CHANGEAGENTID

        SET @i = @i + 1

    END
end