CODEWITHSUNDEEP
sqlsql-server
saran
saran

Reputation: 45

Re write case statement in Where clause

How to re write the following sql server case statement used in where clause. This causes performance degradation.

   SELECT mode,m_name from mst_mode a 
   WHERE
       CASE @mode
          WHEN 'K' THEN 'Y'
                   ELSE ISNULL(a.MODE, 'N')
          END = CASE @mode
                   WHEN 'K' THEN 'Y'
                            ELSE @mode
   END

Upvotes: 1

Views: 79

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

Try this.

SELECT mode,
       m_name
FROM   mst_mode a
WHERE  ( @mode = 'K' )
        OR ( @mode <> 'K' and Isnull(a.MODE, 'N') = @mode )

Upvotes: 3

Related Questions