USP_DISCOUNT_DELETE

CREATE procedure dbo.USP_DISCOUNT_DELETE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as
    set nocount on;

    -- If there are programs linked to the discount, do not allow user to delete it.

    if exists (
        select DISCOUNT.ID
        from dbo.DISCOUNT
        inner join dbo.DISCOUNTGROUP on DISCOUNT.ID = DISCOUNTGROUP.DISCOUNTID
        inner join dbo.DISCOUNTGROUPDETAIL on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
        inner join dbo.DISCOUNTGROUPDETAILPROGRAM on DISCOUNTGROUPDETAIL.ID = DISCOUNTGROUPDETAILPROGRAM.ID
        where DISCOUNT.ID = @ID
    )
    begin
        raiserror('ERR_DISCOUNTDELETE_PROGRAM', 13, 1)
    end


    -- Handle same table reference (SUPERSEDEDBYID column).

    -- Could have done more concisely with a recursive call, but we would almost definitely hit the nesting level limit (32).

    -- Instead, we're going to start with the very first discount and then walk forward until we hit the most recent version

    -- or an error (which is more likely, since most discounts have been used).


    declare @ORIGINALDISCOUNTID uniqueidentifier = (select ORIGINALDISCOUNTID from dbo.DISCOUNT where ID = @ID);
    declare @SUPERSEDEDBYID uniqueidentifier = (select SUPERSEDEDBYID from dbo.DISCOUNT where ID = @ORIGINALDISCOUNTID);

    while @SUPERSEDEDBYID is not null
    begin
        exec dbo.USP_DISCOUNT_DELETEBYID_WITHCHANGEAGENTID @ORIGINALDISCOUNTID, @CHANGEAGENTID;

        select @ORIGINALDISCOUNTID = @SUPERSEDEDBYID;
        select @SUPERSEDEDBYID = SUPERSEDEDBYID from dbo.DISCOUNT where ID = @SUPERSEDEDBYID;
    end

    exec dbo.USP_DISCOUNT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

    return 0;