USP_DATALIST_BUSINESSPROCESSEXPORTMULTIPLEPREVIEW

Displays a list of distinct values from a column and record counts for each. This represents all files to be downloaded for a business process export.

Parameters

Parameter	Parameter Type	Mode	Description
@BUSINESSPROCESSSTATUSID	uniqueidentifier	IN	Business process status ID
@TABLEKEY	nvarchar(50)	IN	Table key
@COLUMNNAME	nvarchar(255)	IN	Column name
@MAXROWS	int	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



        CREATE procedure dbo.[USP_DATALIST_BUSINESSPROCESSEXPORTMULTIPLEPREVIEW]
        (
          @BUSINESSPROCESSSTATUSID uniqueidentifier,
          @TABLEKEY nvarchar(50),
          @COLUMNNAME nvarchar(255),
          @MAXROWS int = 500
        )
        as
          set nocount on;

          declare @RETTABLE table([VALUE] nvarchar(255), [RECORDS] int);

          if len(@BUSINESSPROCESSSTATUSID) > 0 and len(@TABLEKEY) > 0 and len(@COLUMNNAME) > 0
            begin
              declare @TABLENAME nvarchar(128);
              declare @SQL nvarchar(max);

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

              --Check if the tablename and columnname exist first...

              if exists(select 1 from [INFORMATION_SCHEMA].[COLUMNS] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TABLENAME and [COLUMN_NAME] = @COLUMNNAME)
                begin
                  --Get the distinct number of files to be downloaded...

                  set @SQL = 'select top(' + cast(@MAXROWS as nvarchar(10)) + ') isnull(cast([' + @COLUMNNAME + '] as nvarchar(255)),''''), count(*)' + char(13) +
                             'from dbo.[' + @TABLENAME + ']' + char(13) +
                             'group by [' + @COLUMNNAME + ']' + char(13) +
                             'order by [' + @COLUMNNAME + ']';

                  insert into @RETTABLE
                    exec (@SQL);
                end
            end

          select [VALUE], [RECORDS]
          from @RETTABLE;

          return 0;