how to include the column name in case statement
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
Select *
From dbo.MyTable
Where Case When @Test = 1 Then Col1 Else Col2 End > 100
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'
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.
精彩评论