1

I apologize for the title - i was struggling coming up with something better.

Been doing some research on this and did find some close examples, however this is not quite what i need.

Basically i have a table with two columns that i want to evaluate under certain conditions. Column 1 is a identifier that can also be null. Column 2 is a SessionId that can change also.

Primarily i key off of column1, but when column1 is null i would like to key off of column2 instead. The example i linked above doesn't change the column being evaluated in the WHERE clause, only the value being used to evaluate the clause.

Here is some pseudo code to illustrate what i am trying to do:

SELECT * FROM MyTable
WHERE
IF Column1 NOT NULL
   Column1 = @myvariable1
ELSE
   Column2 LIKE '%' + @myvariable2 + '%'

Is something like this even possible? Can i switch the column to be evaluated in the WHERE clause based on the value of one of the columns?

I hope all that makes sense.

TIA

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Nugs
  • 5,503
  • 7
  • 36
  • 57

3 Answers3

4

You could use CASE:

SELECT * 
FROM MyTable
WHERE (CASE WHEN Column1 IS NOT NULL AND Column1 = @myvariable1 THEN 1
            WHEN Column2 LIKE '%' + @myvariable2 + '%' THEN 1
            ELSE 0
      END) = 1;
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
1

You should check for nullable on Column1 on both validation of the where clause in case you set @myvariable1 to null.

SELECT * FROM MyTable
WHERE (Column1 IS NOT NULL AND Column1 = @myvariable1)
OR (Column1 IS NULL and Column2 LIKE '%' + @myvariable2 + '%')
Bae
  • 88
  • 6
0
WHERE  Column1 = @myvariable1
OR   ( Column1 IS NULL AND Column2 LIKE '%' + @myvariable2 + '%' )
paparazzo
  • 44,497
  • 23
  • 105
  • 176