USP_DATALIST_CURRENCYEXCHANGERATEDOWNLOADPROCESSOUTPUT

Returns a status list for each of the rates downloaded by a currency exchange rate download process.

Parameters

Parameter	Parameter Type	Mode	Description
@BUSINESSPROCESSSTATUSID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_CURRENCYEXCHANGERATEDOWNLOADPROCESSOUTPUT]
(
  @BUSINESSPROCESSSTATUSID uniqueidentifier
)
as
  begin
    declare @OUTPUTTABLENAME nvarchar(255);
    declare @SQL nvarchar(max);

    if exists (select top 1 1 from dbo.[BUSINESSPROCESSSTATUS] where [ID] = @BUSINESSPROCESSSTATUSID and [STATUSCODE] = 0) -- only completed processes should return this information

      begin
        select @OUTPUTTABLENAME = [TABLENAME] from dbo.[BUSINESSPROCESSOUTPUT] where [BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID;

        set @SQL = 'select' + char(13) +
                   '  case when [FROM].[ID] is null then ''<currency deleted>'' else [FROM].[NAME] + '' ('' + [FROM].[ISO4217] + '')'' end as [FROMCURRENCY],' + char(13) +
                   '  case when [TO].[ID] is null then ''<currency deleted>'' else [TO].[NAME] + '' ('' + [TO].[ISO4217] + '')'' end as [TOCURRENCY],' + char(13) +
                   '  [OUTPUT].[PARAMETERS],' + char(13) +
                   '  [OUTPUT].[STATUS]' + char(13) +
                   'from dbo.[' + @OUTPUTTABLENAME + '] as [OUTPUT]' + char(13) +
                   'left outer join dbo.[CURRENCY] as [FROM] on [FROM].[ID] = [OUTPUT].[FROMCURRENCYID]' + char(13) +
                   'left outer join dbo.[CURRENCY] as [TO] on [TO].[ID] = [OUTPUT].[TOCURRENCYID]';

        exec sp_executesql @SQL, N'@BUSINESSPROCESSSTATUSID uniqueidentifier', @BUSINESSPROCESSSTATUSID = @BUSINESSPROCESSSTATUSID;
      end
    else
      select '', '', '', '' from dbo.[CURRENCYEXCHANGERATEDOWNLOADPROCESSRATE] where 1 = 0;
  end