USP_PARTIALINFORMATIONSEARCHRESULT_REALESTATEDETAIL_SELECT

            CREATE procedure dbo.USP_PARTIALINFORMATIONSEARCHRESULT_REALESTATEDETAIL_SELECT(
                @ID uniqueidentifier
            )
            as
            begin
                select 
                    PISRRE.JOBID,
                    PISRRE.FIPSCODE,
                    case 
                        when PISRRED.PARTIALINFORMATIONSEARCHRESULT_REALESTATEID is null then 0 
                        else 1 
                    end HASDETAILRECORD,
                    PISRRE.ASSESSORSPARCELNUMBER,
                    dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(PISRRE.PROPERTYADDRESSBLOCK, PISRRE.PROPERTYUNITNUMBER, PISRRE.PROPERTYCITY, PISRRE.PROPERTYSTATE, PISRRE.PROPERTYPOSTCODE) PROPERTYADDRESS,
                    PISRRED.PROPERTYCOUNTY,
                    dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(PISRRE.MAILINGADDRESSBLOCK, PISRRE.MAILINGUNITNUMBER, PISRRE.MAILINGCITY, PISRRE.MAILINGSTATE, PISRRE.MAILINGPOSTCODE) MAILINGADDRESS,
                    PISRRE.OWNERNAME,
                    PISRRED.OWNERCAREOFNAME,
                    PISRRED.ASSESSEENAME,
                    PISRRED.ASSESSEENAME2,
                    PISRRED.ASSESSEECAREOFNAME,
                    dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(PISRRED.ASSESSEEMAILINGADDRESSBLOCK, PISRRED.ASSESSEEMAILINGADDRESSUNITNUMBER, PISRRED.ASSESSEEMAILINGCITY, PISRRED.ASSESSEEMAILINGSTATE, PISRRED.ASSESSEEMAILINGPOSTCODE) ASSESSEEMAILINGADDRESS,
                    dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDNAME(PISRRED.BUYERFIRSTNAME, PISRRED.BUYERLASTNAME) BUYERNAME,
                    dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDNAME(PISRRED.BUYER2FIRSTNAME, PISRRED.BUYER2LASTNAME) BUYER2NAME,
                    PISRRED.BUYERCAREOFNAME,
                    dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(PISRRED.BUYERMAILINGADDRESSBLOCK, PISRRED.BUYERMAILINGADDRESSUNITNUMBER, PISRRED.BUYERMAILINGCITY, PISRRED.BUYERMAILINGSTATE, PISRRED.BUYERMAILINGPOSTCODE) BUYERMAILINGADDRESS,
                    PISRRED.ESTIMATEDVALUE,
                    PISRRED.ESTIMATEDVALUEDATE,
                    PISRRED.CONFIDENCESCORE,
                    PISRRED.CONFIDENCESCOREDATE,
                    PISRRED.ASSESSEDTOTALVALUE,
                    PISRRED.ASSESSMENTYEAR,
                    PISRRED.TOTALMARKETVALUE,
                    PISRRED.MARKETVALUEYEAR,
                    PISRRE.PROPERTYADDRESSBLOCK,
                    PISRRE.PROPERTYUNITNUMBER,
                    PISRRE.PROPERTYCITY,
                    PISRRE.PROPERTYSTATE,
                    PISRRE.PROPERTYPOSTCODE
                from 
                    dbo.PARTIALINFORMATIONSEARCHRESULT_REALESTATE PISRRE
                left join dbo.PARTIALINFORMATIONSEARCHRESULT_REALESTATEDETAIL PISRRED
                    on PISRRE.ID = PISRRED.PARTIALINFORMATIONSEARCHRESULT_REALESTATEID
                where
                    PISRRE.ID = @ID

                return 0;
            end