USP_CONSTITUENTDATAREVIEW_ADDRESS_CHECKGUIDS

create procedure dbo.USP_CONSTITUENTDATAREVIEW_ADDRESS_CHECKGUIDS (
    @INVALIDFIELDS nvarchar(256) output,
    @ADDRESSTYPECODEID uniqueidentifier = null output,
    @COUNTRYID uniqueidentifier = null output,
    @STATEID uniqueidentifier = null output,
    @COUNTYCODEID uniqueidentifier = null output,
    @CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
    @STATEHOUSEDISTRICTCODEID uniqueidentifier = null output,
    @STATESENATEDISTRICTCODEID uniqueidentifier = null output,
    @LOCALPRECINCTCODEID uniqueidentifier = null output,
    @INFOSOURCECODEID uniqueidentifier = null output,
    @REGIONCODEID uniqueidentifier = null output,
    @DONOTMAILREASONCODEID uniqueidentifier = null output
)
as
begin
    declare @C tinyint;

    set @INVALIDFIELDS = '';

    if @ADDRESSTYPECODEID is not null
    begin
        select @C = count(*)
        from dbo.ADDRESSTYPECODE
        where ID = @ADDRESSTYPECODEID;

        if @C = 0
        begin
            set @INVALIDFIELDS = @INVALIDFIELDS + ',Type';
            set @ADDRESSTYPECODEID = null;
        end
    end

    if @COUNTRYID is not null
    begin
        select @C = count(*)
        from dbo.COUNTRY
        where ID = @COUNTRYID;

        if @C = 0
        begin
            set @INVALIDFIELDS = @INVALIDFIELDS + ',Country';
            set @COUNTRYID = null;
        end
    end

    if @STATEID is not null
    begin
        select @C = count(*)
        from dbo.STATE
        where ID = @STATEID;

        if @C = 0
        begin
            set @INVALIDFIELDS = @INVALIDFIELDS + ',State';
            set @STATEID = null;
        end
    end

    if @COUNTYCODEID is not null
    begin
        select @C = count(*)
        from dbo.COUNTYCODE
        where ID = @COUNTYCODEID;

        if @C = 0
        begin
            set @INVALIDFIELDS = @INVALIDFIELDS + ',County';
            set @COUNTYCODEID = null;
        end
    end

    if @CONGRESSIONALDISTRICTCODEID is not null
    begin
        select @C = count(*)
        from dbo.CONGRESSIONALDISTRICTCODE
        where ID = @CONGRESSIONALDISTRICTCODEID;

        if @C = 0
        begin
            set @INVALIDFIELDS = @INVALIDFIELDS + ',Congressional district';
            set @CONGRESSIONALDISTRICTCODEID = null;
        end
    end

    if @STATEHOUSEDISTRICTCODEID is not null
    begin
        select @C = count(*)
        from dbo.STATEHOUSEDISTRICTCODE
        where ID = @STATEHOUSEDISTRICTCODEID;

        if @C = 0
        begin
            set @INVALIDFIELDS = @INVALIDFIELDS + ',State house district';
            set @STATEHOUSEDISTRICTCODEID = null;
        end
    end

    if @STATESENATEDISTRICTCODEID is not null
    begin
        select @C = count(*)
        from dbo.STATESENATEDISTRICTCODE
        where ID = @STATESENATEDISTRICTCODEID;

        if @C = 0
        begin
            set @INVALIDFIELDS = @INVALIDFIELDS + ',State senate district';
            set @STATESENATEDISTRICTCODEID = null;
        end
    end

    if @LOCALPRECINCTCODEID is not null
    begin
        select @C = count(*)
        from dbo.LOCALPRECINCTCODE
        where ID = @LOCALPRECINCTCODEID;

        if @C = 0
        begin
            set @INVALIDFIELDS = @INVALIDFIELDS + ',Local precinct';
            set @LOCALPRECINCTCODEID = null;
        end
    end

    if @INFOSOURCECODEID is not null
    begin
        select @C = count(*)
        from dbo.INFOSOURCECODE
        where ID = @INFOSOURCECODEID;

        if @C = 0
        begin
            set @INVALIDFIELDS = @INVALIDFIELDS + ',Information source';
            set @INFOSOURCECODEID = null;
        end
    end

    if @REGIONCODEID is not null
    begin
        select @C = count(*)
        from dbo.REGIONCODE
        where ID = @REGIONCODEID;

        if @C = 0
        begin
            set @INVALIDFIELDS = @INVALIDFIELDS + ',Region';
            set @REGIONCODEID = null;
        end
    end

    if @DONOTMAILREASONCODEID is not null
    begin
        select @C = count(*)
        from dbo.DONOTMAILREASONCODE
        where ID = @DONOTMAILREASONCODEID;

        if @C = 0
        begin
            set @INVALIDFIELDS = @INVALIDFIELDS + ',Do not mail reason';
            set @DONOTMAILREASONCODEID = null;
        end
    end

    if @INVALIDFIELDS <> ''
        set @INVALIDFIELDS = substring(@INVALIDFIELDS,2,len(@INVALIDFIELDS)-1);
end