USP_DATAFORMTEMPLATE_VIEW_BUSINESSPROCESSEXPORTMULTIPLE

The load procedure used by the view dataform template "Business Process Export Multiple Files View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(500)	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@FILECOUNT	int	INOUT	File count

Definition

Copy



        CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_BUSINESSPROCESSEXPORTMULTIPLE]
        (
          @ID nvarchar(500),                   /* This ID is expected in this format:   BUSINESSPROCESSSTATUSID|TABLEKEY|COLUMNNAME */
          @DATALOADED bit = 0 output,
          @FILECOUNT int = null output
        )
        as
          set nocount on;

          declare @BUSINESSPROCESSSTATUSID nvarchar(36);
          declare @TABLEKEY nvarchar(50);
          declare @COLUMNNAME nvarchar(255);
          declare @POS1 int;
          declare @POS2 int;

          set @DATALOADED = 0;

          --Parse the ID to get the 3 params we need...

          set @POS1 = charindex('|', @ID);
          if @POS1 > 0
            begin
              set @BUSINESSPROCESSSTATUSID = left(@ID, @POS1 - 1);
              set @POS2 = charindex('|', @ID, @POS1 + 1);
              if @POS2 > 0
                begin
                  set @TABLEKEY = substring(@ID, @POS1 + 1, @POS2 - @POS1 - 1);
                  set @POS1 = charindex('|', @ID, @POS1 + len(@TABLEKEY));
                  set @COLUMNNAME = substring(@ID, @POS1 + 1, 255);
                end
            end

          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, make sure to include any null values...

                  set @SQL = 'select @FILECOUNT = count(distinct(isnull(cast([' + @COLUMNNAME + '] as nvarchar(max)),''''))) from dbo.[' + @TABLENAME + ']';
                  exec sp_executesql @SQL, N'@FILECOUNT int output', @FILECOUNT = @FILECOUNT output;

                  set @DATALOADED = 1;
                end
            end

          return 0;