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 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89
| USE [suivi]
GO
/****** Object: StoredProcedure [dbo].[spSGM_GetMvtfabr] Script Date: 07/18/2014 14:31:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Objet : Procédure stockée dbo.spSGM_GetMvtfabr Date du script : 18/04/2003 11:03:50 ******/
CREATE PROCEDURE [dbo].[spSGM_GetMvtfabr]
@ni_num VARCHAR(23),
@pt_num VARCHAR(18)=NULL,
@mf_trans VARCHAR(25)=NULL,
@mf_statut VARCHAR(5)= NULL,
@pr_num INT=NULL,
@nbMax INT=1
AS
DECLARE @iTypeRequete INT
-- Test des paramètres d'entrée pour orientier vers la bonne requêtes
SET @iTypeRequete =
CASE
-- ni_num, pt_num, mf_trans , mf_statut , pr_num renseignés
WHEN (@ni_num IS NOT NULL) AND (@pt_num IS NOT NULL) AND (@mf_trans IS NOT NULL) AND (@mf_statut IS NOT NULL) AND (@pr_num IS NOT NULL)
THEN 0
-- ni_num, pt_num, mf_trans , mf_statut renseigné
WHEN (@ni_num IS NOT NULL) AND (@pt_num IS NOT NULL) AND (@mf_trans IS NOT NULL) AND (@mf_statut IS NOT NULL)
THEN 1 -- Requête sur le n° de série et son code produit , la transaction et son statut
-- ni_num, pt_num, mf_trans renseigné
WHEN (@ni_num IS NOT NULL) AND (@pt_num IS NOT NULL) AND (@mf_trans IS NOT NULL)
THEN 2 -- Requête sur le n° de série et son code produit et la transaction
-- ni_num, pt_num renseigné
WHEN (@ni_num IS NOT NULL) AND (@pt_num IS NOT NULL)
THEN 3 -- Requête sur le n° de série et son code produit
-- ni_num renseigné
WHEN (@ni_num IS NOT NULL) AND (@pt_num IS NULL)
THEN 4 -- Requête sur le n° de série seul
ELSE -1
END
-- on ne renvoie que le dernier par défaut ou un max spécifié
SET ROWCOUNT @nbMax
IF @iTypeRequete = 0
BEGIN
SELECT pt_num,mf_dmvtdeb,ni_num,mf_trans,pr_num,mf_statut,mf_obs,mf_qte
FROM mvtfabr
WHERE ni_num = @ni_num AND pt_num = @pt_num AND mf_trans = @mf_trans AND mf_statut = @mf_statut AND pr_num = @pr_num
ORDER BY mf_dmvtdeb DESC
END
IF @iTypeRequete = 1
BEGIN
SELECT pt_num,mf_dmvtdeb,ni_num,mf_trans,pr_num,mf_statut,mf_obs,mf_qte
FROM mvtfabr
WHERE ni_num = @ni_num AND pt_num = @pt_num AND mf_trans = @mf_trans AND mf_statut = @mf_statut
ORDER BY mf_dmvtdeb DESC
END
IF @iTypeRequete = 2
BEGIN
SELECT pt_num,mf_dmvtdeb,ni_num,mf_trans,pr_num,mf_statut,mf_obs,mf_qte
FROM mvtfabr
WHERE ni_num = @ni_num AND pt_num = @pt_num AND mf_trans = @mf_trans
ORDER BY mf_dmvtdeb DESC
END
IF @iTypeRequete = 3
BEGIN
SELECT pt_num,mf_dmvtdeb,ni_num,mf_trans,pr_num,mf_statut,mf_obs,mf_qte
FROM mvtfabr
WHERE ni_num = @ni_num AND pt_num = @pt_num
ORDER BY mf_dmvtdeb DESC
END
IF @iTypeRequete = 4
BEGIN
SELECT pt_num,mf_dmvtdeb,ni_num,mf_trans,pr_num,mf_statut,mf_obs,mf_qte
FROM mvtfabr
WHERE ni_num = @ni_num
ORDER BY mf_dmvtdeb DESC
END
SET ROWCOUNT 0
RETURN(0)
GO |
Partager