USP_CONSTITUENT_VALIDWEALTHPOINTSEARCHCRITERIA

            CREATE procedure dbo.USP_CONSTITUENT_VALIDWEALTHPOINTSEARCHCRITERIA
            (
                @CONSTITUENTID uniqueidentifier,
                @STRICT bit = null output,
                @RELAXED bit = null output
            )
            as
                set nocount on;

                declare @NAME bit;
                declare @ADDRESS bit;
                declare @PHONE bit;
                declare @DOB bit;
                declare @BUSINESS bit;

                select
                    @NAME = case when len(C.FIRSTNAME)>0 then 1 else 0 end,
                    @ADDRESS = case when len(A.ADDRESSBLOCK)>0 and len(A.CITY)>0 and A.STATEID is not null and len(A.POSTCODE)>0 then 1 else 0 end,
                    @PHONE = case when len(P.NUMBER)>0 then 1 else 0 end,
                    @DOB = case when C.BIRTHDATE!=N'00000000' then 1 else 0 end,
                    @BUSINESS = case when len(CBUSINESS.NAME)>0 then 1 else 0 end
                from
                    dbo.CONSTITUENT C
                    left outer join dbo.ADDRESS A on A.CONSTITUENTID=C.ID and A.ISPRIMARY=1
                    left outer join dbo.PHONE P on P.CONSTITUENTID=C.ID and P.ISPRIMARY=1
                    left outer join dbo.RELATIONSHIP RBUSINESS on C.ID = RBUSINESS.RECIPROCALCONSTITUENTID and RBUSINESS.ISPRIMARYBUSINESS=1
                    left outer join dbo.CONSTITUENT CBUSINESS on CBUSINESS.ID=RBUSINESS.RELATIONSHIPCONSTITUENTID
                where
                    C.ID = @CONSTITUENTID;

                set @RELAXED = coalesce(@NAME,0);
                set @STRICT = coalesce(@NAME,0) & (coalesce(@ADDRESS,0) | coalesce(@PHONE,0) | coalesce(@DOB,0) | coalesce(@BUSINESS,0));

                return 0;