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
| ALTER PROCEDURE [dbo].[WiQSearch]
@Search nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
SELECT Profil.IdProfil, Profil.DateMiseVigueur, Profil.UserIdSibelga AS UserId, Profil.Nom, Profil.Prenom,
COALESCE(RTRIM(Departement.NomFR) + '/' + RTRIM(Departement.NomNL), RTRIM(Departement.NomFR), RTRIM(Departement.NomNL)) AS Departement,
COALESCE(RTRIM(Service.NomFR) + '/' + RTRIM(Service.NomNL), RTRIM(Service.NomFR), RTRIM(Service.NomNL)) AS Service
FROM Profil INNER JOIN
ProfilInterne ON Profil.IdProfil = ProfilInterne.IdProfil AND Profil.DateMiseVigueur = ProfilInterne.DateMiseVigueur INNER JOIN
Service ON Profil.IdService = Service.IdService INNER JOIN
Departement ON Service.IdDepartement = Departement.IdDepartement INNER JOIN
Fonction ON ProfilInterne.IdFonction = Fonction.IdFonction INNER JOIN
LocalisationBatiment ON Profil.IdLocalisationBatiment is NULL OR Profil.IdLocalisationBatiment = LocalisationBatiment.IdLocalisationBatiment
WHERE Profil.UserIdSibelga LIKE '%' + @Search +'%' OR Profil.Nom LIKE '%' + @Search +'%' OR Profil.Prenom LIKE '%' + @Search +'%' OR Profil.NumTelFixe LIKE '%' + @Search +'%' OR Profil.NumGSM LIKE '%' + @Search +'%' OR Profil.NumFax LIKE '%' + @Search +'%' OR LocalisationBatiment.Nom LIKE '%' + @Search +'%'
OR Fonction.NomFR LIKE '%' + @Search +'%' OR Fonction.NomNL LIKE '%' + @Search +'%'
OR Departement.NomFR LIKE '%' + @Search +'%' OR Departement.NomNL LIKE '%' + @Search +'%' OR Departement.CodeDepartement LIKE '%' + @Search +'%'
AND ProfilActif = 1 AND (GETDATE() < Profil.DateFin)
UNION
SELECT Profil.IdProfil, Profil.DateMiseVigueur, Profil.UserIdSibelga AS UserId, Profil.Nom, Profil.Prenom,
COALESCE(RTRIM(Departement.NomFR) + '/' + RTRIM(Departement.NomNL), RTRIM(Departement.NomFR), RTRIM(Departement.NomNL)) AS Departement,
COALESCE(RTRIM(Service.NomFR) + '/' + RTRIM(Service.NomNL), RTRIM(Service.NomFR), RTRIM(Service.NomNL)) AS Service
FROM Profil INNER JOIN
ProfilExterne ON Profil.IdProfil = ProfilExterne.IdProfil AND Profil.DateMiseVigueur = ProfilExterne.DateMiseVigueur INNER JOIN
Service ON Profil.IdService = Service.IdService INNER JOIN
Departement ON Service.IdDepartement = Departement.IdDepartement INNER JOIN
LocalisationBatiment ON Profil.IdLocalisationBatiment is NULL OR Profil.IdLocalisationBatiment = LocalisationBatiment.IdLocalisationBatiment
WHERE Profil.UserIdSibelga LIKE '%' + @Search +'%' OR Profil.Nom LIKE '%' + @Search +'%' OR Profil.Prenom LIKE '%' + @Search +'%' OR Profil.NumTelFixe LIKE '%' + @Search +'%' OR Profil.NumGSM LIKE '%' + @Search +'%' OR Profil.NumFax LIKE '%' + @Search +'%' OR LocalisationBatiment.Nom LIKE '%' + @Search +'%'
OR ProfilExterne.Fonction LIKE '%' + @Search +'%'
OR Departement.NomFR LIKE '%' + @Search +'%' OR Departement.NomNL LIKE '%' + @Search +'%' OR Departement.CodeDepartement LIKE '%' + @Search +'%'
AND ProfilActif = 1 AND (GETDATE() < Profil.DateFin)
ORDER BY UserIdSibelga
END |
Partager