USP_CONSTITUENTSOURCEANALYSIS_UPDATESMARTFIELD

            CREATE procedure dbo.USP_CONSTITUENTSOURCEANALYSIS_UPDATESMARTFIELD
            (
                @CACHETABLENAME nvarchar(128),
                @SMARTFIELDDISPLAYNAME nvarchar(100),
                @DATATYPE nvarchar(20),
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
            set nocount on;

            begin try
                if @DATATYPE <> 'int' and @DATATYPE <> 'nvarchar' and @DATATYPE <> 'datetime' and @DATATYPE <> 'money' and @DATATYPE <> 'bit'
                    raiserror('Invalid data type. Valid options: int, nvarchar, datetime, money, bit', 13, 1);

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
                if @CHANGEDATE is null
                    set @CHANGEDATE = getdate();

                declare @SMARTFIELDINSTANCEID uniqueidentifier;
                declare @SMARTFIELDDATE datetime;
                declare @SMARTFIELDTABLENAME nvarchar(128);

                select top 1 @SMARTFIELDTABLENAME = TABLECATALOG.TABLENAME, @SMARTFIELDDATE = coalesce(SMARTFIELD.LASTRUNON, getdate()) from SMARTFIELD
                inner join dbo.SMARTFIELDCATALOG on SMARTFIELDCATALOG.ID = SMARTFIELDCATALOGID
                inner join dbo.TABLECATALOG on TABLECATALOG.ID = SMARTFIELD.TABLECATALOGID
                where SMARTFIELDCATALOG.DISPLAYNAME like @SMARTFIELDDISPLAYNAME
                order by SMARTFIELD.LASTRUNON desc

                if len(@SMARTFIELDTABLENAME) > 0
                begin
                    declare @SQL nvarchar(max);

                    set @SQL = 'update dbo.' + @CACHETABLENAME + ' set NEEDSUPDATE = 0, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE where NEEDSUPDATE = 1';
                    exec sp_executesql @SQL, N'@CHANGEAGENTID uniqueidentifier, @CHANGEDATE datetime', @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE;

                    set @SQL = 
                        '
                        with [CTE] as 
                        ( 
                            select 
                                CONSTITUENT.ID as [CONSTITUENTID], 
                                coalesce([SMARTFIELDTABLE].VALUE, cast(0 as ' + @DATATYPE + ')) as [VALUE]
                            from dbo.CONSTITUENT
                            left join dbo.' + @SMARTFIELDTABLENAME + ' as [SMARTFIELDTABLE] on [SMARTFIELDTABLE].ID = CONSTITUENT.ID
                            inner join dbo.' + @CACHETABLENAME + ' as [CACHETABLE] on [CACHETABLE].CONSTITUENTID = CONSTITUENT.ID
                            where @SMARTFIELDDATE > [CACHETABLE].DATEFROM
                        )
                        update dbo.' + @CACHETABLENAME + ' 
                            set NEEDSUPDATE = 1,
                                DATETO = @SMARTFIELDDATE,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CHANGEDATE
                        from ' + @CACHETABLENAME +' as [CACHETABLE]
                        inner join [CTE] on [CTE].CONSTITUENTID = [CACHETABLE].CONSTITUENTID and [CACHETABLE].DATETO is null
                        where
                            [CTE].VALUE <> [CACHETABLE].VALUE
                        '

                    exec sp_executesql @SQL, N'@CHANGEAGENTID uniqueidentifier, @CHANGEDATE datetime, @SMARTFIELDDATE datetime', @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE, @SMARTFIELDDATE = @SMARTFIELDDATE;

                    set @SQL = 
                        '
                        with [CTE] as
                        (
                            select
                                CONSTITUENT.ID as [CONSTITUENTID],
                                coalesce([SMARTFIELDTABLE].VALUE, cast(0 as ' + @DATATYPE + ')) as [VALUE]
                            from dbo.CONSTITUENT
                            left join ' + @SMARTFIELDTABLENAME + ' as [SMARTFIELDTABLE] on [SMARTFIELDTABLE].ID = CONSTITUENT.ID
                            left join ' + @CACHETABLENAME + ' as [CACHETABLE] on [CACHETABLE].CONSTITUENTID = CONSTITUENT.ID
                            where [CACHETABLE].ID is null or [CACHETABLE].NEEDSUPDATE = 1
                        )
                        insert into dbo.' + @CACHETABLENAME + ' (ID, NEEDSUPDATE, CONSTITUENTID, DATEFROM, DATETO, VALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            select
                                newid(),
                                0,
                                [CTE].CONSTITUENTID,
                                @SMARTFIELDDATE,
                                null,
                                [CTE].VALUE,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CHANGEDATE,
                                @CHANGEDATE
                            from [CTE]
                        ';

                    exec sp_executesql @SQL, N'@CHANGEAGENTID uniqueidentifier, @CHANGEDATE datetime, @SMARTFIELDDATE datetime', @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE, @SMARTFIELDDATE = @SMARTFIELDDATE;        
                end

            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;