2
votes

Comment réécrire l'instruction "where, and, or" à partir de ce code laid

mon code fonctionne mais est horrible. y a-t-il un moyen de nettoyer ce code de manière appropriée?

ce code (proc stocké) est utilisé pour rechercher un mot dans différentes colonnes d'une table et des articles qui ont un ID de rôle différent (0-4)

@searchval nvarchar(500),
@arrStatusTyp int,
@rolltypAdmin int,
@roll1 int,
@roll2 int,
@roll3 int,
@roll4 int,
@visningsperiod nvarchar(4)

SELECT  kk_aj_tbl_Arrangemang.ArrID    
    FROM    kk_aj_tbl_content INNER JOIN
                kk_aj_tbl_arridtoContent ON kk_aj_tbl_content.Contentid = kk_aj_tbl_arridtoContent.contentid INNER JOIN
                kk_aj_tbl_Arrangemang ON kk_aj_tbl_arridtoContent.arrid = kk_aj_tbl_Arrangemang.ArrID INNER JOIN
                kk_aj_tbl_ArrangemangStatus ON kk_aj_tbl_Arrangemang.ArrangemangStatusID = kk_aj_tbl_ArrangemangStatus.ArrangemangStatusID INNER JOIN
                kk_aj_tbl_Konstformtyp ON kk_aj_tbl_Arrangemang.KonstformID = kk_aj_tbl_Konstformtyp.KonstformID INNER JOIN
                Users ON kk_aj_tbl_Arrangemang.AdminuserID = Users.UserID INNER JOIN
                kk_aj_tbl_utovare ON kk_aj_tbl_Arrangemang.UtovarID = kk_aj_tbl_utovare.UtovarID
    WHERE  
        (kk_aj_tbl_Arrangemang.ArrangemangStatusID = @arrStatusTyp) AND (kk_aj_tbl_arridtoContent.Version=1) AND 
        (
            (kk_aj_tbl_content.Rubrik LIKE '%'+@searchval+'%') AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll1) OR
            (kk_aj_tbl_content.Rubrik LIKE '%'+@searchval+'%') AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll2) OR
            (kk_aj_tbl_content.Rubrik LIKE '%'+@searchval+'%') AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll3) OR
            (kk_aj_tbl_content.Rubrik LIKE '%'+@searchval+'%') AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll4) OR                 
            (kk_aj_tbl_content.Underrubrik LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll1) OR
            (kk_aj_tbl_content.Underrubrik LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll2) OR
            (kk_aj_tbl_content.Underrubrik LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll3) OR
            (kk_aj_tbl_content.Underrubrik LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll4) OR
            (kk_aj_tbl_utovare.Organisation LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll1) OR
            (kk_aj_tbl_utovare.Organisation LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll2) OR
            (kk_aj_tbl_utovare.Organisation LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll3) OR
            (kk_aj_tbl_utovare.Organisation LIKE '%'+@searchval+'%')AND (kk_aj_tbl_Konstformtyp.KonstformID = @roll4) 
        )
    ORDER BY kk_aj_tbl_arridtoContent.datum DESC

merci pour toute l'aide

sql tsql

1 commentaires

Pourquoi n'utilisez-vous pas (kk_aj_tbl_content.Rubrik LIKE '%' + @ searchval + '%') AND (kk_aj_tbl_Konstformtyp.KonstformID dans (@ roll1, @ roll2, @ roll3, @ roll4) ?

4 Réponses :

3
votes

Conditions telles que:

...
WHERE  
kk_aj_tbl_Arrangemang.ArrangemangStatusID = @arrStatusTyp
AND kk_aj_tbl_arridtoContent.Version=1
AND kk_aj_tbl_Konstformtyp.KonstformID IN (@roll1, @roll2, @roll3, @roll4)           
AND ( 
    kk_aj_tbl_content.Rubrik LIKE '%'+@searchval+'%'
    OR kk_aj_tbl_content.Underrubrik LIKE '%'+@searchval+'%'
    OR kk_aj_tbl_content.Organisation LIKE '%'+@searchval+'%'
)

Peut être réécrite comme:

cond1 AND (cond2 OR cond3 OR cond4)

Essayez donc la clause WHERE suivante :

(cond1 OR cond2)
AND (cond1 OR cond3)
AND (cond1 OR cond4)
...

1 commentaires

Bon !!! Cela peut également résoudre un problème de performances car la clause OR dans le WHERE peut entraîner de graves problèmes de performances.

2
votes

Commencez par utiliser un alias de table personnalisé pour ne pas avoir à répéter le nom complet de la table (utilisez un nom significatif, pas A, B, C comme mon exemple). Vous pouvez réécrire vos multiples OU comme IN:

WHERE  
    A.ArrangemangStatusID = @arrStatusTyp AND 
    D.Version = 1 AND 
    (
        (B.Rubrik LIKE '%' + @searchval + '%' AND E.KonstformID IN (@roll1, @roll2, @roll3, @roll4)) OR
        (B.Underrubrik LIKE '%' + @searchval + '%' AND E.KonstformID IN (@roll1, @roll2, @roll3, @roll4)) OR
        (C.Organisation LIKE '%' + @searchval + '%' AND E.KonstformID IN (@roll1, @roll2, @roll3, @roll4))
    )

Vous n'avez pas besoin de mettre toutes les conditions entre parenthèses, utilisez-les uniquement lorsque nécessaire.

0 commentaires

0
votes

SELECT kk_aj_tbl_arrangemang.arrid 
  FROM kk_aj_tbl_content 
       INNER JOIN kk_aj_tbl_arridtocontent 
               ON kk_aj_tbl_content.contentid = kk_aj_tbl_arridtocontent.contentid 
       INNER JOIN kk_aj_tbl_arrangemang 
               ON kk_aj_tbl_arridtocontent.arrid = kk_aj_tbl_arrangemang.arrid 
       INNER JOIN kk_aj_tbl_arrangemangstatus 
               ON kk_aj_tbl_arrangemang.arrangemangstatusid = kk_aj_tbl_arrangemangstatus.arrangemangstatusid
       INNER JOIN kk_aj_tbl_konstformtyp 
               ON kk_aj_tbl_arrangemang.konstformid = kk_aj_tbl_konstformtyp.konstformid 
       INNER JOIN users 
               ON kk_aj_tbl_arrangemang.adminuserid = users.userid 
       INNER JOIN kk_aj_tbl_utovare 
               ON kk_aj_tbl_arrangemang.utovarid = kk_aj_tbl_utovare.utovarid 
 WHERE ( kk_aj_tbl_arrangemang.arrangemangstatusid = @arrStatusTyp ) 
   AND ( kk_aj_tbl_arridtocontent.version = 1 ) 
   AND ( ( kk_aj_tbl_content.rubrik LIKE '%' + @searchval + '%' ) 
         AND ( kk_aj_tbl_konstformtyp.konstformid = @roll1 ) 
          OR ( kk_aj_tbl_content.rubrik LIKE '%' + @searchval + '%' ) 
             AND ( kk_aj_tbl_konstformtyp.konstformid = @roll2 ) 
          OR ( kk_aj_tbl_content.rubrik LIKE '%' + @searchval + '%' ) 
             AND ( kk_aj_tbl_konstformtyp.konstformid = @roll3 ) 
          OR ( kk_aj_tbl_content.rubrik LIKE '%' + @searchval + '%' ) 
             AND ( kk_aj_tbl_konstformtyp.konstformid = @roll4 ) 
          OR ( kk_aj_tbl_content.underrubrik LIKE '%' + @searchval + '%' ) 
             AND ( kk_aj_tbl_konstformtyp.konstformid = @roll1 ) 
          OR ( kk_aj_tbl_content.underrubrik LIKE '%' + @searchval + '%' ) 
             AND ( kk_aj_tbl_konstformtyp.konstformid = @roll2 ) 
          OR ( kk_aj_tbl_content.underrubrik LIKE '%' + @searchval + '%' ) 
             AND ( kk_aj_tbl_konstformtyp.konstformid = @roll3 ) 
          OR ( kk_aj_tbl_content.underrubrik LIKE '%' + @searchval + '%' ) 
             AND ( kk_aj_tbl_konstformtyp.konstformid = @roll4 ) 
          OR ( kk_aj_tbl_utovare.organisation LIKE '%' + @searchval + '%' ) 
             AND ( kk_aj_tbl_konstformtyp.konstformid = @roll1 ) 
          OR ( kk_aj_tbl_utovare.organisation LIKE '%' + @searchval + '%' ) 
             AND ( kk_aj_tbl_konstformtyp.konstformid = @roll2 ) 
          OR ( kk_aj_tbl_utovare.organisation LIKE '%' + @searchval + '%' ) 
             AND ( kk_aj_tbl_konstformtyp.konstformid = @roll3 ) 
          OR ( kk_aj_tbl_utovare.organisation LIKE '%' + @searchval + '%' ) 
             AND ( kk_aj_tbl_konstformtyp.konstformid = @roll4 ) ) 
 ORDER BY kk_aj_tbl_arridtocontent.datum DESC

0 commentaires

0
votes

Utilisation de l'alias de table et simplification de la clause WHERE:

SELECT  am.ArrID    
FROM    kk_aj_tbl_content c
INNER JOIN kk_aj_tbl_arridtoContent a2c     ON  c.Contentid     = a2c.contentid
INNER JOIN kk_aj_tbl_Arrangemang am         ON  a2c.arrid   = am.ArrID 
INNER JOIN kk_aj_tbl_ArrangemangStatus ams  ON am.ArrangemangStatusID = ams.ArrangemangStatusID 
INNER JOIN kk_aj_tbl_Konstformtyp kft       ON am.KonstformID = kft.KonstformID 
INNER JOIN Users u                          ON am.AdminuserID = u.UserID 
INNER JOIN kk_aj_tbl_utovare uto            ON am.UtovarID = uto.UtovarID
WHERE am.ArrangemangStatusID = @arrStatusTyp
AND a2c.Version = 1 
AND kft.KonstformID in (@roll1,@roll2,@roll3,@roll4)
AND (
            c.Rubrik LIKE '%'+@searchval+'%'        OR
            c.Underrubrik LIKE '%'+@searchval+'%'   OR
            uto.Organisation LIKE '%'+@searchval+'%'
)
ORDER BY a2c.datum DESC

0 commentaires