USP_DATALIST_REGISTRANTATTRIBUTEDATA

CREATE procedure dbo.USP_DATALIST_REGISTRANTATTRIBUTEDATA
(
  @ATTRIBUTECATALOGID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @EVENTID uniqueidentifier
)
as
    set nocount on;

      DECLARE @ID varchar(32)
      DECLARE @REGISTRANTID uniqueidentifier
      DECLARE @sqlCommand nvarchar(4000)
      DECLARE @DATATYPE tinyint
    DECLARE @CODETABLEDBTABLENAME varchar(200)
    DECLARE @CODETABLECATALOGID uniqueidentifier
    DECLARE @ONLYALLOWONEPERRECORD bit

    SELECT @DATATYPE=DATATYPECODE,@CODETABLECATALOGID=CODETABLECATALOGID from ATTRIBUTECATEGORY WHERE ID=@ATTRIBUTECATALOGID

    SELECT @CODETABLEDBTABLENAME=DBTABLENAME FROM CODETABLECATALOG WHERE ID=@CODETABLECATALOGID

    SELECT @ONLYALLOWONEPERRECORD=ONLYALLOWONEPERRECORD FROM ATTRIBUTECATEGORY WHERE ID=@ATTRIBUTECATALOGID

      SET @ID=replace(CAST(@ATTRIBUTECATALOGID as CHAR(36)),'-','')

      SELECT @REGISTRANTID=ID FROM REGISTRANT WHERE EVENTID=@EVENTID AND CONSTITUENTID=@CONSTITUENTID

      SET @sqlCommand='SELECT TOP 1 '

      if @DATATYPE=4 
          SET @sqlCommand=@sqlCommand + 'A.ID,A.BOOLEANCODE AS Value from ATTRIBUTE'
    else if @DATATYPE=5 and @ONLYALLOWONEPERRECORD=1
          SET @sqlCommand=@sqlCommand + 'A.ID, dbo.UFN_'+@CODETABLEDBTABLENAME+'_GETDESCRIPTION(A.'+@CODETABLEDBTABLENAME+'ID) as VALUE from ATTRIBUTE'
      else if @DATATYPE=5 and @ONLYALLOWONEPERRECORD=0
          SET @sqlCommand=@sqlCommand + 'A.ID,B.DESCRIPTION AS VALUE from ATTRIBUTE'
      else if @DATATYPE=5 
          SET @sqlCommand=@sqlCommand + 'A.ID,CAST(A.'+@CODETABLEDBTABLENAME+'ID as CHAR(36)) AS Value from ATTRIBUTE'
      else if @DATATYPE=6
          SET @sqlCommand=@sqlCommand + 'A.ID,CAST(A.CONSTITUENTVALUEID as CHAR(36)) AS Value from ATTRIBUTE'
      else
          SET @sqlCommand=@sqlCommand + 'A.ID,A.Value from ATTRIBUTE'

    SET @sqlCommand=@sqlCommand + @ID+' A '

    if @DATATYPE=5 and @ONLYALLOWONEPERRECORD=0
          SET @sqlCommand=@sqlCommand+' INNER JOIN '+@CODETABLEDBTABLENAME+' AS B ON A.'+@CODETABLEDBTABLENAME+'ID=B.ID'

    IF @ONLYALLOWONEPERRECORD=1
          SET @sqlCommand=@sqlCommand + ' where A.ID='''
    ELSE      
          SET @sqlCommand=@sqlCommand + ' where A.REGISTRANTID='''

    SET @sqlCommand=@sqlCommand + CAST(@REGISTRANTID as CHAR(36)) + ''''

    SET @sqlCommand=@sqlCommand + ' ORDER BY A.DATECHANGED DESC'

    exec sp_executesql @sqlCommand