CODEWITHSUNDEEP
sqlsql-server
user1989
user1989

Reputation: 217

SQL Case statement not working

I am writing a sql query and it works, but I get only half results: 

  (q1.VariableName LIKE
        CASE WHEN @Variablename IS NULL THEN ('e%')
        ELSE @Variablename
        END)

The thing I want is : If the @Variablename is NOT NULL then q1.VariableName = @Variablename.

If it is NULL,
 then q1.VariableName like 'e%' 
 or q1.VariableName like 'b%' and q1.VARIABLENAME not LIKE '%MAIL%'

But, I am unable to get it. Can anyone tell me, where am I going wrong.

Upvotes: 0

Views: 69

Answers (3)

DBNull
DBNull

Reputation: 86

You can use the isnull() function to check for NULL. If @VariableName is NULL the function will replace it with 'e%'. 

WHERE q1.VariableName LIKE isnull(@VariableName, 'e%')

If it's NOT NULL it would have to be equal to q1.VariableName, provided @VariableName doesn't contain a wildcard.

Upvotes: 2

radar
radar

Reputation: 13425

OR can be used in the where clause

where (  q1.VariableName = @Variablename)
      OR ( @VariableName IS  NULL And (q1.VariableName like 'e%' 
 or q1.VariableName like 'b%' and q1.VARIABLENAME not LIKE '%MAIL%'))

Upvotes: 1

Allan
Allan

Reputation: 17429

If you're doing this is the where clause, you would be better off avoiding using CASE altogether:

   ((@Variablename IS NULL
     and (q1.VariableName LIKE 'e%'
          or q1.VariableName like 'b%' 
          and q1.VARIABLENAME not LIKE '%MAIL%'))
or q1.VariableName = @Variablename)

Since null is never equal to anything, q1.VariableName = @Variablename) will always evaluate to false when @Variablename is null.

Upvotes: 0

Related Questions