UFN_DISCOUNT_UNIQUEMERCHANDISEDEPARTMENTCHECK

create function dbo.UFN_DISCOUNT_UNIQUEMERCHANDISEDEPARTMENTCHECK
(
    @DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENTID uniqueidentifier
)
returns bit
with execute as caller
as begin
    declare @MERCHANDISEID uniqueidentifier;
    declare @DISCOUNTID uniqueidentifier;
    declare @DISCOUNTEDITEM tinyint;

    select @MERCHANDISEID = DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.MERCHANDISEDEPARTMENTID,
        @DISCOUNTEDITEM = DISCOUNTEDITEM,
        @DISCOUNTID = DISCOUNTGROUP.DISCOUNTID
    from dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT
    inner join dbo.DISCOUNTGROUPDETAIL
        on DISCOUNTGROUPDETAIL.ID = DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.ID
    inner join dbo.DISCOUNTGROUP 
        on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
    where DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.ID = @DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENTID;

    if exists (    select 1
                from dbo.DISCOUNTGROUP DG
                inner join dbo.DISCOUNTGROUPDETAIL DGD 
                    on DGD.DISCOUNTGROUPID = DG.ID
                inner join dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT DGDMI 
                    on DGDMI.ID = DGD.ID
                where
                    DG.DISCOUNTID = @DISCOUNTID and
                    DGD.DISCOUNTEDITEM = @DISCOUNTEDITEM and
                    DGDMI.MERCHANDISEDEPARTMENTID = @MERCHANDISEID and
                    DGDMI.ID <> @DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENTID)
    begin
        return 0 
    end
    else
    begin
        return 1 
    end
    return 0
end