how to include the column name in case statement

Question

Hi, I need to include a column name in the where clause of SQL query depending on a variable value. For Example, 1. If I have a variable,say,@test and I assigned the variable with the value 1/0. 2. I have a table ,say, table1 with 2 column names col1,col2.

Now, I am writing a query to fetch some data from the table by including only one column at a time depends on the @test variable(declared earlier) i.e. I need to include col1 in the where clause of the query if the @test is 1 and col2 if @test is 0.

Please let me know the solution asap.

Thanks in advance

Answer 1

Select *
From dbo.MyTable
Where Case When @Test = 1 Then Col1 Else Col2 End  > 100

Answer 2

declare @tbl table
(
    col1 int,
    col2 int

)

insert into @tbl select 10, 20

declare @test int

set @test = 1

select *
from @tbl
Where Case When @test = 1 Then Col1 Else Col2 End  = 10

If datatype is different

select *
from @tbl
Where Case when @test = 1 Then Col1 end = 10
        OR
      Case when @test = 2 Then Col2 end = 'sometext'

Answer 3

A slightly different approach:

Select *
From dbo.MyTable
Where Col1 = Case When @Test = 1 Then 10 Else Col1 End and
      Col2 = Case When @Test = 0 Then 'sometext' Else Col2 End 

This version of the query may be sargable.