USP_SMARTFIELD_CONSTITUENTRECOGNITIONCONSECUTIVEYEAR

        create procedure dbo.USP_SMARTFIELD_CONSTITUENTRECOGNITIONCONSECUTIVEYEAR
        (
            @SMARTFIELDID uniqueidentifier = null,
            @RECOGNITIONPROGRAMID uniqueidentifier = null,
            @ASOF datetime 
        )
        as
        set nocount on;

        declare @SQL nvarchar(max);
        declare @TABLENAME nvarchar(256);
        declare @ROWCOUNT int;

        select @TABLENAME = TABLECATALOG.TABLENAME
        from dbo.SMARTFIELD
        inner join dbo.TABLECATALOG on TABLECATALOG.ID = SMARTFIELD.TABLECATALOGID
        where SMARTFIELD.ID = @SMARTFIELDID;

        -- delete old records

        set @SQL ='truncate table dbo.' +  @TABLENAME;

        exec sp_executesql @SQL;

        -- insert new records

        set @SQL = 'insert into dbo.' + @TABLENAME + '(ID, VALUE)
        select
        CR.CONSTITUENTID,
        dbo.UFN_CONSTITUENTRECOGNITION_CONSECUTIVEYEARS(CR.ID)
        from
        dbo.UFN_CONSTITUENT_GETCURRENTRECOGNITIONS(getdate(),@RECOGNITIONPROGRAMID) CURRENTRECOGNITIONS
        inner join dbo.CONSTITUENTRECOGNITION CR on CR.ID = CURRENTRECOGNITIONS.ID
        where 
        CR.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID;'

        exec sp_executesql @SQL, N'@RECOGNITIONPROGRAMID uniqueidentifier', @RECOGNITIONPROGRAMID;

        set @SQL = 'select @ROWCOUNT = count(1) from dbo.' + @TABLENAME;

        exec sp_executesql @SQL, N'@ROWCOUNT int output', @ROWCOUNT = @ROWCOUNT output;

        select @ROWCOUNT;