UFN_DISCOUNT_UNIQUEPROGRAMCHECK

CREATE function dbo.UFN_DISCOUNT_UNIQUEPROGRAMCHECK
(
    @DISCOUNTGROUPDETAILPROGRAMID uniqueidentifier
)
returns bit
with execute as caller
as begin
    declare @PROGRAMID uniqueidentifier;
    declare @DISCOUNTID uniqueidentifier;
    declare @ISDISCOUNTEDITEM bit;

    select @PROGRAMID = PROGRAMID from dbo.DISCOUNTGROUPDETAILPROGRAM where ID = @DISCOUNTGROUPDETAILPROGRAMID;

    select
        @DISCOUNTID = DG.DISCOUNTID,
        @ISDISCOUNTEDITEM = DGD.DISCOUNTEDITEM
    from dbo.DISCOUNTGROUPDETAIL DGD
        inner join dbo.DISCOUNTGROUP DG on DG.ID = DGD.DISCOUNTGROUPID
    where
        DGD.ID = @DISCOUNTGROUPDETAILPROGRAMID

    if exists (
        select DGDP.ID
        from dbo.DISCOUNTGROUP DG
            inner join dbo.DISCOUNTGROUPDETAIL DGD on DGD.DISCOUNTGROUPID = DG.ID
            inner join dbo.DISCOUNTGROUPDETAILPROGRAM DGDP on DGD.ID = DGDP.ID
        where
            DG.DISCOUNTID = @DISCOUNTID
            and DGD.DISCOUNTEDITEM = @ISDISCOUNTEDITEM
            and DGDP.PROGRAMID = @PROGRAMID
            and DGDP.ID <> @DISCOUNTGROUPDETAILPROGRAMID
    )
    begin
        return 0 --this program already exists on this discount

    end
    else
    begin
        return 1 --good to go

    end
    return 0
end