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 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
|
SET isolation level REPEATABLE READ
Begin transaction;
CREATE PROCEDURE [dbo].[spSGM_GetDonneesCapabilite]
@dateDebut datetime,
@dateFin datetime,
@Transaction varchar(30),
@LpNum varchar(2),
@TypeProduit varchar(20)=NULL
AS
IF (@TypeProduit IS NULL)
SELECT distinct mvttest.pt_num, vSGM_produit.pt_lib, mt_testeur, vSGM_Postes.pr_lib, me_libelle
FROM Mvttest
INNER JOIN Detailmesure ON DetailMesure.mt_id = mvttest.mt_id
INNER JOIN Mesure on detailmesure.me_id = mesure.me_id
INNER JOIN vSGM_produit ON MvtTest.pt_num = vSGM_produit.pt_num
LEFT JOIN vSGM_Postes on Mvttest.mt_testeur COLLATE SQL_Latin1_General_CP1_CI_AS = vSGM_Postes.pr_IDreseau
WHERE MvtTest.mt_datedeb >= @datedebut
and mvttest.mt_datedeb <=@dateFin
and MvtTest.mt_test=@transaction
and vSGM_produit.lp_num=@LpNum
and sp_id in('PROD','PROTO')
order by MvtTest.pt_num, MvtTest.mt_testeur, me_libelle
ELSE
IF (@TypeProduit <> 'Standard')
SELECT distinct mvttest.pt_num, vSGM_produit.pt_lib, mt_testeur, vSGM_Postes.pr_lib, me_libelle
FROM Mvttest
INNER JOIN Detailmesure ON DetailMesure.mt_id = mvttest.mt_id
INNER JOIN Mesure on detailmesure.me_id = mesure.me_id
INNER JOIN vSGM_produit ON MvtTest.pt_num = vSGM_produit.pt_num
LEFT JOIN vSGM_Postes on Mvttest.mt_testeur COLLATE SQL_Latin1_General_CP1_CI_AS = vSGM_Postes.pr_IDreseau
WHERE MvtTest.mt_datedeb >= @datedebut
and mvttest.mt_datedeb <=@dateFin
and MvtTest.mt_test=@transaction
and vSGM_produit.lp_num=@LpNum
and sp_id in('PROD','PROTO')
AND mp_cod like '%' + @Typeproduit + '%'
order by MvtTest.pt_num, MvtTest.mt_testeur, me_libelle
ELSE
BEGIN
SELECT distinct mvttest.pt_num, vSGM_produit.pt_lib, mt_testeur, vSGM_Postes.pr_lib, me_libelle
FROM Mvttest
INNER JOIN Detailmesure ON DetailMesure.mt_id = mvttest.mt_id
INNER JOIN Mesure on detailmesure.me_id = mesure.me_id
INNER JOIN vSGM_produit ON MvtTest.pt_num = vSGM_produit.pt_num
LEFT JOIN vSGM_Postes on Mvttest.mt_testeur COLLATE SQL_Latin1_General_CP1_CI_AS = vSGM_Postes.pr_IDreseau
WHERE MvtTest.mt_datedeb >= @datedebut
and mvttest.mt_datedeb <=@dateFin
and MvtTest.mt_test=@transaction
and vSGM_produit.lp_num=@LpNum
and sp_id in('PROD','PROTO')
AND mp_cod IN('ADSL', 'ADSL2+')
order by MvtTest.pt_num, MvtTest.mt_testeur, me_libelle
END
commit transaction; |
Partager