USP_SIMPLEDATALIST_BUSINESSPROCESSEXPORT_OUTPUTCOLUMNNAMES

CREATE procedure dbo.[USP_SIMPLEDATALIST_BUSINESSPROCESSEXPORT_OUTPUTCOLUMNNAMES]
(
  @BUSINESSPROCESSSTATUSID uniqueidentifier,
  @TABLEKEY nvarchar(50),
  @COLUMNSTOSUPPRESS nvarchar(max) = null
)
as
  set nocount on;

  declare @TABLENAME nvarchar(128);
  declare @PKCONSTRAINTNAME nvarchar(128);
  declare @PKCOLUMNNAME nvarchar(128);
  declare @SUPPRESSCOLUMNS table ([TABLE_SCHEMA] nvarchar(128), [COLUMN_NAME] nvarchar(128));
  declare @SUPPRESSCOLUMNSXML xml;
  declare @SUPPRESSCOLUMN nvarchar(128);
  declare @DOTPOSITION integer;
  declare @COLUMNMETADATA xml;

  select
    @TABLENAME = [TABLENAME],
    @COLUMNMETADATA = [COLUMNMETADATA]
  from dbo.[BUSINESSPROCESSOUTPUT]
  where [BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID
  and upper([TABLEKEY]) = upper(@TABLEKEY);

  select @PKCONSTRAINTNAME = [CONSTRAINT_NAME]
  from [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]
  where [TABLE_SCHEMA] = 'dbo' and [CONSTRAINT_SCHEMA] = 'dbo'
  and [TABLE_NAME] = @TABLENAME
  and [CONSTRAINT_TYPE] = 'PRIMARY KEY';

  if @PKCONSTRAINTNAME is not null
    select @PKCOLUMNNAME = [COLUMN_NAME]
    from [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
    where [TABLE_SCHEMA] = 'dbo' and [CONSTRAINT_SCHEMA] = 'dbo'
    and [TABLE_NAME] = @TABLENAME
    and [CONSTRAINT_NAME] = @PKCONSTRAINTNAME;

  if @COLUMNSTOSUPPRESS is not null and len(ltrim(rtrim(@COLUMNSTOSUPPRESS))) > 0
    begin
      /* columns to suppress can be expressed schema.columnname,schema.columnname or just columnname,columnname
         with the schema dbo implied */

      set @SUPPRESSCOLUMNSXML = cast(N'<ITEM>' + replace(@COLUMNSTOSUPPRESS, N',', N'</ITEM><ITEM>') + N'</ITEM>' as xml);

      insert into @SUPPRESSCOLUMNS ([COLUMN_NAME])
      select ltrim(rtrim(T.c.value('.', 'nvarchar(128)')))
      from @SUPPRESSCOLUMNSXML.nodes('/ITEM') as T(c);

      declare SCHEMACURSOR cursor local fast_forward for
        select [COLUMN_NAME] from @SUPPRESSCOLUMNS;

      open SCHEMACURSOR;
      fetch next from SCHEMACURSOR into @SUPPRESSCOLUMN;

      while (@@FETCH_STATUS = 0)
        begin
          set @DOTPOSITION = charindex(N'.', @SUPPRESSCOLUMN);

          if @DOTPOSITION > 0
            update @SUPPRESSCOLUMNS set 
              [TABLE_SCHEMA] = ltrim(rtrim(left(@SUPPRESSCOLUMN, @DOTPOSITION - 1))),
              [COLUMN_NAME] = ltrim(rtrim(right(@SUPPRESSCOLUMN, len(@SUPPRESSCOLUMN) - @DOTPOSITION)))
            where [COLUMN_NAME] = @SUPPRESSCOLUMN;

          fetch next from SCHEMACURSOR into @SUPPRESSCOLUMN;
        end

      close SCHEMACURSOR;
      deallocate SCHEMACURSOR;

      /* since the SQL below specifies [TABLE_SCHEMA] = 'dbo', take out any suppressed columns with a schema other than dbo specified */
      delete from @SUPPRESSCOLUMNS
      where [TABLE_SCHEMA] is not null and len([TABLE_SCHEMA]) > 0 and [TABLE_SCHEMA] <> 'dbo';
    end

  if not @COLUMNMETADATA is null
    insert into @SUPPRESSCOLUMNS
    select 'dbo', T.c.value('@ColumnName', 'nvarchar(128)')
    from @COLUMNMETADATA.nodes('/ColumnMetaData/Columns/Column') T(c)
    where T.c.value('@Suppress', 'nvarchar(5)') = 'true'

  select
    [C].[COLUMN_NAME] as [VALUE],
    [C].[COLUMN_NAME] as [LABEL]
  from [INFORMATION_SCHEMA].[COLUMNS] as [C]
  left outer join @SUPPRESSCOLUMNS as [S] on [S].[COLUMN_NAME] = [C].[COLUMN_NAME]
  where [C].[TABLE_SCHEMA] = 'dbo'
  and [C].[TABLE_NAME] = @TABLENAME
  and (@PKCOLUMNNAME is null or [C].[COLUMN_NAME] <> @PKCOLUMNNAME)
  and [S].[COLUMN_NAME] is null
  order by [C].[COLUMN_NAME];

  return 0;