USP_BBDW_SMARTFIELD_GETVALUES

create procedure dbo.[USP_BBDW_SMARTFIELD_GETVALUES](@SMARTFIELDID uniqueidentifier)
as 
  --This NOP is required for the SSIS development environment to be able to detect the result set column metadata.

  if 1=null
    select newid() [SMARTFIELDSYSTEMID], newid() [ID], cast('No Value Group' as nvarchar(100)) [VALUEGROUP], cast('No Value' as nvarchar(255)) [VALUE]

  declare @SQL nvarchar(500);
  declare @TABLENAME nvarchar(100);
  declare @DATATYPECODE int;
  declare @HASVALUEGROUP bit;

  select 
    @TABLENAME = 'V_QUERY_' + t.[TABLENAME],
    @HASVALUEGROUP = s.[USEVALUEGROUP],
    @DATATYPECODE = sc.[DATATYPECODE]
  from dbo.[SMARTFIELD] s
  inner join dbo.[SMARTFIELDCATALOG] sc on s.[SMARTFIELDCATALOGID] = sc.[ID]
  inner join dbo.[TABLECATALOG] t on s.[TABLECATALOGID] = t.[ID]
  where s.[ID] = @SMARTFIELDID;

  if @HASVALUEGROUP = 1
  begin
    set @sql = 'select @SMARTFIELDID [SMARTFIELDSYSTEMID], [ID], case when [VALUEGROUP] = '''' or [VALUEGROUP] is null then ''No Value Group'' else  [VALUEGROUP] end as [VALUEGROUP], [VALUE] from  dbo.[' + @TABLENAME + '];';

    exec sp_executesql @SQL, N'@SMARTFIELDID uniqueidentifier',@SMARTFIELDID = @SMARTFIELDID;   
  end
  else if @DATATYPECODE = 4
  begin
    set @sql = 'select @SMARTFIELDID [SMARTFIELDSYSTEMID], [ID], case when [VALUE] = 1 then ''Yes'' when [VALUE] = 0 then ''No'' else ''No Value Group'' end [VALUEGROUP], [VALUE] from  dbo.[' + @TABLENAME + '];';

    exec sp_executesql @SQL, N'@SMARTFIELDID uniqueidentifier',@SMARTFIELDID = @SMARTFIELDID;
  end
  else
  begin
    set @sql = 'select @SMARTFIELDID [SMARTFIELDSYSTEMID], [ID], ''No Value Group'' [VALUEGROUP], [VALUE] from  dbo.[' + @TABLENAME + '];';

    exec sp_executesql @SQL, N'@SMARTFIELDID uniqueidentifier',@SMARTFIELDID = @SMARTFIELDID;     
  end