USP_DATALIST_CONSTITUENTATTRIBUTEDATA

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

    DECLARE @ID varchar(32)
    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)),'-','')

      DECLARE @sqlCommand nvarchar(4000)

      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=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.CONSTITUENTID='''

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

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

    exec sp_executesql @sqlCommand