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
|
-- Paramètres
@idLigneDesign INT,
@nom VARCHAR(50),
@real INT,
@typeExec INT
DECLARE @final VARCHAR(1000)
SET @final = ''
IF @real IS NULL
BEGIN
IF @nom IS NULL OR @nom = '@NULL@'
BEGIN
IF @typeExec IS NULL
BEGIN
SELECT @final = @final + COALESCE(f.nom_fctrole + ISNULL(' (' + f1.nom_fctrole + ')', '') + ISNULL(' (' + CONVERT(VARCHAR(10), l.dtprev_lignedesigntrt, 103) + ')', '') + ', ', '')
FROM bado.dbo.T_DesignTrt r
INNER JOIN bado.dbo.T_FctRole f ON r.nom_designtrt = f.chiffre_fctrole and f.id_type = 82
LEFT OUTER JOIN bado.dbo.T_Fctrole f1 ON r.type_exec_designtrt = f1.id_fctrole
INNER JOIN bado.dbo.T_LigneDesignTrt l ON r.id_designtrt = l.id_designtrt AND l.id_lignedesign = @idLigneDesign
WHERE r.type_exec_designtrt IN (SELECT id_fctrole FROM bado.dbo.T_Param_Vue_Arc)
END
ELSE
BEGIN
SELECT @final = @final + COALESCE(f.nom_fctrole + ISNULL(' (' + f1.nom_fctrole + ')', '') + ISNULL(' (' + CONVERT(VARCHAR(10), l.dtprev_lignedesigntrt, 103) + ')', '') + ', ', '')
FROM bado.dbo.T_DesignTrt r
INNER JOIN bado.dbo.T_FctRole f ON r.nom_designtrt = f.chiffre_fctrole and f.id_type = 82
LEFT OUTER JOIN bado.dbo.T_Fctrole f1 ON r.type_exec_designtrt = f1.id_fctrole
INNER JOIN bado.dbo.T_LigneDesignTrt l ON r.id_designtrt = l.id_designtrt AND l.id_lignedesign = @idLigneDesign
WHERE r.type_exec_designtrt = @typeExec AND r.type_exec_designtrt IN (SELECT id_fctrole FROM bado.dbo.T_Param_Vue_Arc)
END
END
ELSE
BEGIN
IF @typeExec IS NULL
BEGIN
SELECT @final = @final + COALESCE(f.nom_fctrole + ISNULL(' (' + f1.nom_fctrole + ')', '') + ISNULL(' (' + CONVERT(VARCHAR(10), l.dtprev_lignedesigntrt, 103) + ')', '') + ', ', '')
FROM bado.dbo.T_DesignTrt r
INNER JOIN bado.dbo.T_FctRole f ON r.nom_designtrt = f.chiffre_fctrole and f.id_type = 82 AND f.nom_fctrole like '%' + @nom + '%'
LEFT OUTER JOIN bado.dbo.T_Fctrole f1 ON r.type_exec_designtrt = f1.id_fctrole
INNER JOIN bado.dbo.T_LigneDesignTrt l ON r.id_designtrt = l.id_designtrt AND l.id_lignedesign = @idLigneDesign
WHERE r.type_exec_designtrt IN (SELECT id_fctrole FROM bado.dbo.T_Param_Vue_Arc)
END
ELSE
BEGIN
SELECT @final = @final + COALESCE(f.nom_fctrole + ISNULL(' (' + f1.nom_fctrole + ')', '') + ISNULL(' (' + CONVERT(VARCHAR(10), l.dtprev_lignedesigntrt, 103) + ')', '') + ', ', '')
FROM bado.dbo.T_DesignTrt r
INNER JOIN bado.dbo.T_FctRole f ON r.nom_designtrt = f.chiffre_fctrole and f.id_type = 82 AND f.nom_fctrole like '%' + @nom + '%'
LEFT OUTER JOIN bado.dbo.T_Fctrole f1 ON r.type_exec_designtrt = f1.id_fctrole
INNER JOIN bado.dbo.T_LigneDesignTrt l ON r.id_designtrt = l.id_designtrt AND l.id_lignedesign = @idLigneDesign
WHERE r.type_exec_designtrt = @typeExec AND r.type_exec_designtrt IN (SELECT id_fctrole FROM bado.dbo.T_Param_Vue_Arc)
END
END
END
ELSE
BEGIN
IF @real = 1
BEGIN
IF @nom IS NULL OR @nom = '@NULL@'
BEGIN
IF @typeExec IS NULL
BEGIN
[...]
END
IF @final <> ''
SET @final = LEFT(@final, LEN(@final) - 1)
RETURN @final |
Partager