USP_CONSTITUENTS_WITHINRADIUS

CREATE procedure dbo.USP_CONSTITUENTS_WITHINRADIUS
(
    @LATITUDE decimal(16, 13),
    @LONGITUDE decimal(16, 13),
    @RADIUS decimal,
    @IDSETIDS nvarchar(max)
)
as
begin

    declare @RADIUSINKILOMETERS bit;
    select @RADIUSINKILOMETERS = MAPDISTANCEUNITCODE from MAPDISTANCEUNIT;

    declare @idtable table (id uniqueidentifier);
    declare @SQL nvarchar(max) = '';

    declare @idsettable table (idsetid uniqueidentifier);
    declare @delimiter as nvarchar(max) = ',';

    if len(@IDSETIDS) > 0
    begin
        -- Parse each set ID into a table var.

        declare @xml xml
        set @xml = N'<root><r>' + replace(@IDSETIDS,@delimiter,'</r><r>') + '</r></root>'
        insert into 
            @idsettable(idsetid)
        select
            r.value('.','uniqueidentifier') as idsetid
        from
             @xml.nodes('//root/r') as records(r)    

        -- Aggregate set id selection statements, concatenated with 'union'

        select 
            @SQL = @SQL + dbo.ufn_buildselectsql(idsetid) + ' union'
        from 
            @idsettable

        -- Strip trailing 'union' string.

        if LEN(@SQL) > 0
            set @SQL = substring(@SQL, 0, len(@SQL) - 5);

        -- Insert the Ids obtained from dynamic sql into a table var.

        insert into @idtable
        exec (@SQl);
    end

    if @RADIUSINKILOMETERS = 0
    begin
        select 
            CONSTITUENT.ID
        from
            CONSTITUENT
        inner join
            ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and Address.ISPRIMARY = 1
        inner join
            ADDRESSCOORDINATES on ADDRESSCOORDINATES.ADDRESSID = ADDRESS.ID
        inner join
            @idtable as IDTABLE on IDTABLE.ID = CONSTITUENT.ID
        where
            dbo.UFN_COORDINATES_GETDISTANCEINMILES(ADDRESSCOORDINATES.LATITUDE, ADDRESSCOORDINATES.LONGITUDE, @LATITUDE, @LONGITUDE) <= @RADIUS
    option(recompile)
    end
    else
    begin
        select 
            CONSTITUENT.ID
        from
            CONSTITUENT
        inner join
            ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and Address.ISPRIMARY = 1
        inner join
            ADDRESSCOORDINATES on ADDRESSCOORDINATES.ADDRESSID = ADDRESS.ID
        inner join
            @idtable as IDTABLE on IDTABLE.ID = CONSTITUENT.ID
        where
            dbo.UFN_COORDINATES_GETDISTANCEINKILOMETERS(ADDRESSCOORDINATES.LATITUDE, ADDRESSCOORDINATES.LONGITUDE, @LATITUDE, @LONGITUDE) <= @RADIUS
    option(recompile)
    end

end