开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜