1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
| PROCEDURE MaProcedure
@Soc varchar(40),
@Fam varchar(40),
@CodeLangue varchar(40),
@PageIndex int,
@NbrLigne int,
@Cat varchar(40)
AS
SET NOCOUNT ON
DECLARE @StartRowIndex int;
-- Prépare la requête de récupération de l'ensemble des éléments --
WITH Items as(
SELECT ROW_NUMBER() OVER (ORDER BY Articl.Libart) AS Row, LTRIM(ARTICL.ART) AS Art, ARTICL.LIBART AS Libelle, ARTICL.FAMART AS Codefamille,
FAMILL.NOMFAM AS LibelleFamille, SUPFAM.NOMSFA AS LibelleSuperFamille, ARTLEG.LECLEG AS LegendeCourte, Artleg.Lelleg AS LegendeLongue,
case Fabric.MarFab when 'OUI' then Fabric.Nomfab else '' end as FabriquantNom
FROM ARTICL INNER JOIN
FAMILL ON ARTICL.SOCA = FAMILL.SOCA AND ARTICL.FAMART = FAMILL.FAM INNER JOIN
SUPFAM ON ARTICL.SOCA = SUPFAM.SOCA AND SUPFAM.SFA = SUBSTRING(CAST(FAMILL.FAM AS varchar), 1, 1) INNER JOIN
ARTICS ON ARTICL.SOCA = ARTICS.SOCA AND ARTICL.ART = ARTICS.ART INNER JOIN
CATDET ON ARTICS.SOCA = CATDET.SOC AND ARTICS.ART = CATDET.ARTCAD AND ARTICS.ARS = CATDET.ARSCAD LEFT OUTER JOIN
FABRIC ON ARTICL.SOCA = FABRIC.SOCA AND ARTICL.FABART = FABRIC.FAB LEFT OUTER JOIN
ARTLEG ON ARTICL.SOCA = ARTLEG.SOCA AND ARTICL.ART = ARTLEG.ART AND ARTLEG.LANLEG = @CodeLangue
WHERE (FAMILL.SOCA = @Soc) AND (ARTICL.FAMART = @Fam) AND (CATDET.CAT = @Cat)
GROUP BY LTRIM(ARTICL.ART), ARTICL.LIBART, ARTICL.FAMART, FAMILL.NOMFAM, SUPFAM.NOMSFA, ARTLEG.LECLEG, Artleg.Lelleg, Fabric.Nomfab,Fabric.MarFab
)
-- Récupère les éléments attendus
SELECT Art, Libelle, CodeFamille, LibelleSuperFamille, LibelleFamille, LegendeCourte, LegendeLongue, FabriquantNom FROM Items Where Row BETWEEN @StartRowIndex AND @StartRowIndex + @NbrLigne - 1 |
