CODEWITHSUNDEEP
sql-servert-sql
Edit
Edit

Reputation: 393

How to use case clause in where condition using SQL Server?

Hi I am going to filter with one query in SQL Server. but I am getting error some thing like this

Incorrect syntax near '='.

I have one table. In this table store 2 column 1 is socialtype and 2 is ISFUser like this. I want to filter some on this 2 column.my filter param is All,Instagram,ISFUser. so i need to All select then want to all data. then after I select Instagram only then get InstagramUsers. like this.

Here This is my query. 

declare @Filter nvarchar(max) = 'Instagram'
select * from Users 
where IsDeleted = 0 and @Filter = 
case 
when @Filter = 'Instagram' then socialtype 
when @Filter = 'ISFUser' then (ISFUser = 1)
ELSE  @Filter = 'ALL'  then (1 = 1)
END

This is my query I want to apply filter but how can do I have no idea. I am getting error.

Upvotes: 3

Views: 58

Answers (2)

Phylogenesis
Phylogenesis

Reputation: 7880

I think what you're trying to do is the following:

select
    *
from
    Users 
where
    IsDeleted = 0 and (
        @Filter = 'ALL' or
        @Filter = 'ISFUser' and ISFUser = 1 or
        @Filter = 'Instagram' and socialtype = 'Instagram'
    )

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could expand your condition to:

declare @Filter nvarchar(max) = 'Instagram';

select * 
from Users 
where IsDeleted = 0 
  and (
        (@Filter = 'Instagram' and socialtype = 'Instagram')
        OR
        (@Filter = 'ISFUser' AND IFUser = 1)
        OR 
        @Filter = 'ALL'
      );

Upvotes: 3

Related Questions