sql server - Case statement in WHERE clause in SQL -

sql server - Case statement in WHERE clause in SQL -

this question has reply here:

sql switch/case in clause 10 answers

i have table 'route' id ,name , active columns. if id = 0 want select id's active = true.

if id > 0 want select info particular id active = true.

i tried next query. not working. (here passed 2 id value.) can back upwards me?

declare @id int set @id = 2 select distinct id ,name route case when @id > 0 id = @id , active= 1 else active=1 end

sample info attached.

no need utilize case can done using clause only.

declare @id int set @id = 2 select distinct id ,name route active=1 , (id = @id or @id = 0);

explanation:

where case when @id > 0 id = @id , active= 1 else active=1

a. if @id > 0 id = @id , active= 1

b. else active=1

if both cases closely, active= 1 remains same in both cases can taken safely outside of condition

then remaining part is, if @id > 0 id = @id i.e. select row per parameter value when parameter has valid value, otherwise select all.

so, if combine both becomes

where active=1 , (id = @id or @id = 0);

sql sql-server stored-procedures