USP_SIMPLEDATALIST_FAFRegBenefitOptionAttributes

create procedure dbo.USP_SIMPLEDATALIST_FAFRegBenefitOptionAttributes
    (@BENEFITOPTIONID uniqueidentifier,
        @APPUSERID as uniqueidentifier = null)
as
    set nocount on;

        declare @script as nvarchar(4000)
        declare @PARAMDEF as nvarchar(2000)
        declare @tablename nvarchar(200)
        declare @i int
        declare @total int
        declare @category varchar(100)
        declare @DATATYPE varchar(50)
        declare @CODETABLECATALOGID uniqueidentifier
        declare @CODETABLE nvarchar(200)
        declare @VALUECOLUMNNAME varchar(256)

        DECLARE @ATTRIBUTETABLE TABLE (ID int IDENTITY(1,1),TABLENAME nvarchar(100),CATEGORY varchar(100),DATATYPE varchar(50), CODETABLECATALOGID uniqueidentifier, VALUECOLUMNNAME varchar(256))

        INSERT INTO @ATTRIBUTETABLE (TABLENAME,CATEGORY,DATATYPE,CODETABLECATALOGID,VALUECOLUMNNAME)
        select TC.TABLENAME as ATTRIBUTETABLENAME, AC.NAME, AC.DATATYPE, AC.CODETABLECATALOGID, AC.VALUECOLUMNNAME
        from
            dbo.ATTRIBUTECATEGORY AC
            inner join dbo.ATTRIBUTERECORDTYPE ART on ART.ID = AC.ATTRIBUTERECORDTYPEID
            inner join dbo.RECORDTYPE RT on RT.ID = ART.RECORDTYPEID
            inner join dbo.TABLECATALOG TC on TC.ID = AC.TABLECATALOGID 
        WHERE RT.NAME = 'Benefit Option'
            and    dbo.UFN_SECURITY_APPUSER_GRANTED_ATTRIBUTECATEGORY_IN_SYSTEMROLE(@APPUSERID,AC.ID) = 1

        create table #BENEFITATTRIBUTES    (ID uniqueidentifier, VALUE nvarchar(510),TABLENAME nvarchar(100),CATEGORY varchar(100))

        SET @PARAMDEF = '@BENEFITOPTIONID uniqueidentifier, @tablename nvarchar(100), @category varchar(100), @CODETABLE nvarchar(200), @VALUECOLUMNNAME varchar(256) '

        set @i = 1
        select @total = COUNT(*) from @ATTRIBUTETABLE

        WHILE (@i <= @total)
        BEGIN    

            SET @script = ''
            SET @tablename = ''
            SET @DATATYPE = ''
            SET @CODETABLECATALOGID = null
            SET @CODETABLE = ''


            SELECT @tablename = TABLENAME, @category = CATEGORY, @DATATYPE = DATATYPE, 
                @CODETABLECATALOGID = CODETABLECATALOGID,
                @VALUECOLUMNNAME = VALUECOLUMNNAME
            from @ATTRIBUTETABLE where ID = @i

            IF @CODETABLECATALOGID IS NOT NULL
            SELECT @CODETABLE = DBTABLENAME from dbo.CODETABLECATALOG where ID = @CODETABLECATALOGID

            IF @DATATYPE = 'Yes/No'
            SET @script = 'Insert into #BENEFITATTRIBUTES select ID, (CASE BOOLEANCODE when 1 then ''Yes'' else ''No'' END), @tablename, @category from ' + @tablename + ' where BENEFITOPTIONID = @BENEFITOPTIONID'
            IF @DATATYPE in ('Number','Date','Currency','Fuzzy date','Time','Memo','Text')
            SET @script = 'Insert into #BENEFITATTRIBUTES select ID, VALUE, @tablename, @category from ' + @tablename + ' where BENEFITOPTIONID = @BENEFITOPTIONID'
            IF @DATATYPE = 'Code table'
            SET @script = 'Insert into #BENEFITATTRIBUTES select A.ID, C.DESCRIPTION, @tablename, @category from ' + @tablename + ' as A 
                            INNER JOIN ' + @CODETABLE + ' as C ON C.ID = A.'+ @VALUECOLUMNNAME +'
                            where A.BENEFITOPTIONID = @BENEFITOPTIONID'
            IF @DATATYPE = 'Constituent record'
            SET @script = 'Insert into #BENEFITATTRIBUTES select A.ID, C.NAME, @tablename, @category from ' + @tablename + ' as A 
                            INNER JOIN dbo.CONSTITUENT (NOLOCK) as C ON C.ID = A.CONSTITUENTVALUEID
                            where A.BENEFITOPTIONID = @BENEFITOPTIONID'

            exec sp_executesql @script, @PARAMDEF, @BENEFITOPTIONID, @tablename, @category, @CODETABLE, @VALUECOLUMNNAME

            SET @i = @i + 1

        END

      select 
          BA.ID as VALUE, 
          BA.VALUE as LABEL
      from #BENEFITATTRIBUTES BA 
      order by BA.VALUE

    drop table #BENEFITATTRIBUTES