UFN_R68_SITESBYREFERENCENUMBER

            CREATE function dbo.UFN_R68_SITESBYREFERENCENUMBER
            (
                @REFERENCENUMBER nvarchar(20)
            )
            returns @SITESBYREFERENCENUMBER table
            (
                SITEID uniqueidentifier
            )
            as 
            begin
                if not exists (select ID from dbo.CHARITYCLAIMREFERENCENUMBER where REFERENCENUMBER = @REFERENCENUMBER and ID not in
                        (select CHARITYCLAIMREFERENCENUMBERID from dbo.CHARITYCLAIMREFERENCENUMBERSITE))
                    insert into @SITESBYREFERENCENUMBER
                    select CCRNS.SITEID
                    from dbo.CHARITYCLAIMREFERENCENUMBER CCRN
            inner join dbo.CHARITYCLAIMREFERENCENUMBERSITE CCRNS on CCRNS.CHARITYCLAIMREFERENCENUMBERID = CCRN.ID
                    where CCRN.REFERENCENUMBER = @REFERENCENUMBER
                else                
                    insert into @SITESBYREFERENCENUMBER
                    select ID 
                    from dbo.SITE 
                    where ID not in (select CCRNS.SITEID 
                                    from dbo.CHARITYCLAIMREFERENCENUMBER CCRN
                    inner join dbo.CHARITYCLAIMREFERENCENUMBERSITE CCRNS on CCRNS.CHARITYCLAIMREFERENCENUMBERID = CCRN.ID)
                    UNION
                    select null

                return
            end