CODEWITHSUNDEEP
sqlsql-servert-sql
Sergej Scheibel
Sergej Scheibel

Reputation: 63

SQL Server Simplifying WHERE clause

Could anybody help me with this sample statement:

declare @test int = 5
declare @temp table (id int, name varchar(20))

insert into @temp
values (1,'John'), (1,'Jenny')

if (@test = 5)
begin
    select * 
    from @temp a
    where a.id = 1
      and a.name ='Jenny'
end
else
begin
    select * 
    from @temp a
    where a.id = 1
end

I am pretty sure it goes simpler with just one SELECT and IF or CASE in WHERE.

Any ideas?

Thanks in advance!

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

I think you want this:

select a.*
from @temp a
where a.id = 1 and
      (a.name = 'Jenny' or @test <> 5);

Note: if you anticipate that @test could be NULL, then the logic needs to take that into account as well.

Upvotes: 1

Related Questions