SET NOCOUNT ON;

-- Declare @parameters and @command variables
DECLARE @command nvarchar(MAX)
DECLARE @parameters nvarchar(MAX)

-- Set @parameters variable
  SET @parameters = N'@RequestCode nvarchar(255), @ARC nvarchar(255), @vitastor nvarchar(255), @dateFrom Datetime, @dateTo Datetime, @Svice nvarchar(255), @Distributor nvarchar(255), @Seller nvarchar(255), @Flow nvarchar(255)'


-- Set @command variable
SET @command = N'SELECT * FROM
                            (select *,
                               ROW_NUMBER() OVER (ORDER BY  ' + @sortExpression + ') AS RowNum
                               from
                               (select  r.FileID,r.FilePath,r.Vitadst,r.Seller,r.vitaserv,r.Flusso,r.FlagErr,r.DescErr,r.Data,r.vitastor,max(e.FlagErr) as ErroreOutcome,count(*)as NumeroEsiti, max(r.Type) as SviceType
                                    from
                                        (   select distinct rf.FileID,rf.FilePath
                                                ,rf.Vitadst,av.Descrizione as Seller
                                                ,rf.vitaserv,rf.Flusso,rf.FlagErr,rf.DescErr,rf.Data,rf.vitastor, S.Type

                                            from    dbo.colmerich rf WITH (nolock)
                                                    JOIN dbo.fildrich ef WITH (nolock)
                                                    on (rf.FileID = ef.vitafilID) AND (@ARC is null OR @ARC = ef.ARC) AND (@RequestCode is null OR @RequestCode = ef.caseNumber) AND (rf.richturc is null OR (rf.richturc=''GAS''))
                                                    left join  dbo.hubgas_Svices S on (rf.vitaserv collate SQL_Latin1_General_CP1_CI_AS LIKE ''%'' + S.IDSvice + ''%'')
                                                    left join AnagraficaVenditori av with(nolock) on (rf.vitasell = av.vitatbl)
                                            where (@Seller is null OR @Seller = av.vitatbl)
                                         )
                                         r join
                                         dbo.fildrich e WITH (nolock) on (r.FileID = e.vitafilID)
                                group by r.FileID,r.FilePath,r.Vitadst,r.Seller,r.vitaserv,r.Flusso,r.FlagErr,r.DescErr,r.Data,r.vitastor

                                UNION

                                select rf.FileID, rf.FilePath,rf.Vitadst,av.Descrizione as Seller,rf.vitaserv,rf.Flusso,rf.FlagErr,rf.DescErr,rf.Data,rf.vitastor,NULL as ErroreOutcome,0 as NumeroEsiti, s.Type as SviceType
                                from dbo.colmerich rf WITH (nolock)
                                 left join  dbo.hubgas_Svices S WITH (nolock)
                                  on (rf.vitaserv collate SQL_Latin1_General_CP1_CI_AS LIKE ''%'' + S.IDSvice + ''%'')
                                  left join AnagraficaVenditori av with(nolock) on (rf.vitasell = av.vitatbl)
                                where   vitastor != 1
                                        AND Visionato = 0
                                        AND (@ARC is null OR rf.FileID = ''-1'')
                                        AND (@RequestCode is null OR rf.FileID = ''-1'')
                                        AND (rf.richturc is null OR (rf.richturc=''GAS''))
                                        AND (@Seller  is null OR  @Seller = av.vitatbl)
                               ) res
                               where
                                      (@Distributor is null OR @Distributor = res.Vitadst)
                                  AND (@vitastor is null OR @vitastor = vitastor)
                                  AND (@Svice is null OR @Svice = vitaserv)
                                  AND (SviceType is null OR SviceType like ''CUU%'')
                                  AND (@Flow is null OR @Flow = Flusso)
                                  AND (@dateFrom is null OR (Data >= @dateFrom))
                                  AND (@dateTo is null OR (Data <= @dateTo))
                             ) rows
                              WHERE RowNum BETWEEN ' +  @lowerBound + ' AND ' + @upperBound

                            DECLARE @tab AS TABLE (FileID NVARCHAR(255)  null, FilePath NVARCHAR(255) null ,Vitadst NVARCHAR(255), Seller NVARCHAR(255),vitaserv NVARCHAR(255) null , Flusso NVARCHAR(255) null, FlagErr NVARCHAR(255),DescErr NVARCHAR(255) null, Data Datetime null , vitastor NVARCHAR(255) null, ErroreOutcome NVARCHAR(255) null , NumeroEsiti int, SviceType NVARCHAR(255) null, RowNum int )