Parameter from two different columns' value

Transact-SQL
1

Hello SQL expert,

Below is my test query:

Select column1, Column2, Column3, Column4, Column5, Column6
From TblTest

I would like to have above query filter by two fields, Column1 and Column2.

The condition is, if Column1 is null then use value from Column2. How do I write the logic in the WHERE clause to place my parameter (let's called it @Column) so it first look at the value from Column1, if Column1 has null, then use value from Column2.

For example:
Declare @Column varchar(max), @Date datetime

Set @Column = 'Purple'
Set @Date = '01/01/2016'

Select column1, Column2, Column3, Column4, Column5, Column6,Date
From TblTest
Where Column1 in (@Column)
and Date in (@Date)

So, @Date is selected and sometimes, on certain date(s), Column1 may not have the value of PURPLE but Column2 has. So I want to be able to use the value of PURPLE from Column2 if Column1 is not available.

Anyone know how do I add this on my WHERE clause?

Thank you all

2

Where @Column = ISNULL(Column1, Column2)
and Date = @Date /IN (@variable) does not work because @variable is always treated as a single value, even if it has commas in it/

1 Like
3

Thanks Scott,

@Column = ISNULL(Column1, Column2) it works great with NULL value.

I forgot to mentioned one more condition as well. If the values are not PURPLE nor NULL, I also need to look for Column2 for Purple Value as well. For example; if the column has (RED, BLUE, ORANGE, BLACK," " (blank) and so on), look on the Column2.

Basically, if the value is not PURPLE from Column1 then look for Column2.

4

@Column = ISNULL(NULLIF(Column1, 'PURPLE'), Column2)

5

Thanks ScottPletcher. You are smart !!!