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
| ALTER PROCEDURE dbo.TROUVER_SOCIETE2
@_SOCIETE varchar(100) = NULL
, @_VILLE varchar(30) = NULL
, @_CP varchar(10) = NULL
, @_TEL varchar(30) = NULL
, @_FAX varchar(30) = NULL
, @_GROUPEMENT varchar(100) = NULL
, @_AGENT varchar(50) = NULL
, @_ACTIVITE varchar(50) = NULL
, @_PAYS varchar(50) = NULL
, @_un bit = NULL
, @_deux bit = NULL
, @_trois bit = NULL
, @_quatre bit = NULL
, @_aucun bit = NULL
, @_client bit = NULL
, @_prospect bit = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql nvarchar(max)
, @priorite nvarchar(9)
SELECT @sql =
'SELECT S.[ID SOCIETE]
, ISNULL(S.PRIORITE, 0) AS PRIORITE
, CASE S.[POSITION] WHEN ''Client'' THEN ''C'' ELSE '' END AS [POSITION]
, S.[CODE CLIENT],
, S.[CLASSEMENT],
, S.SOCIETE,
, S.CP,
, S.VILLE,
, AC.AGENT AS AGENT
FROM dbo.SOCIETES AS S
LEFT JOIN dbo.[AGENTS COMMERCIAUX] AS AC ON S.AGENT = AC.[ID_AGENT]
WHERE 1 = 1'
IF @_SOCIETE IS NOT NULL
SET @sql = @sql + N' AND S.SOCIETE LIKE ''%' + @_SOCIETE + '%'''
IF @_VILLE IS NOT NULL
SET @sql = @sql + N' AND S.VILLE LIKE ''%' + @_VILLE + '%'''
IF @_CP IS NOT NULL
SET @sql = @sql + N' AND S.CP LIKE ''%' + @_CP + '%'''
IF @_TEL IS NOT NULL
SET @sql = @sql + N' AND S.CP LIKE ''%' + @_TEL + '%'''
IF @_FAX IS NOT NULL
SET @sql = @sql + N' AND S.FAX1 LIKE ''%' + @_FAX + '%'''
IF @_GROUPEMENT IS NOT NULL
SET @sql = @sql + N' AND S.GROUPEMENT LIKE ''%' + @_GROUPEMENT + '%'''
IF @_AGENT IS NOT NULL
SET @sql = @sql + N' AND S.AGENT LIKE ''%' + @_AGENT + '%'''
IF @_ACTIVITE IS NOT NULL
SET @sql = @sql + N' AND S.ACTIVITE LIKE ''%' + @_ACTIVITE + '%'''
IF @_PAYS IS NOT NULL
SET @sql = @sql + N' AND S.PAYS LIKE ''%' + @_PAYS + '%'''
-- Recherche de la priorité
SELECT @priorite = CASE
WHEN @_un = 0 THEN N' > 1 '
WHEN @_deux = 0 THEN N' <> 2 '
WHEN @_trois = 0 THEN N' <> 3 '
WHEN @_quatre = 0 THEN N' < 4 '
ELSE N' IS NULL '
END
SET @sql = @sql + ' AND S.PRIORITE' + @priorite + 'ORDER BY S.SOCIETE'
--PRINT @sql
EXEC sp_executeSQL @sql
END |
Partager