Multiple conditions in WHERE clause

问题:

I have to pass a parameter into a condition to select rows, if I pass 0 it should select all rows, 1 - only HDD, 2 - All except HDD

Please see code:

declare @Accessories table (id int, name nvarchar(20), atype nvarchar(20), itype int)

insert into @Accessories values (1, 'Seagate HDD 100GB', 'HDD', 1)
insert into @Accessories values (2, 'Samsung HDD 100GB', 'HDD', 1)
insert into @Accessories values (3, 'WD HDD 500GB', 'HDD', 1)
insert into @Accessories values (4, 'Samsung 4GB', 'RAM', 2)
insert into @Accessories values (5, 'GeForce 512MB', 'Video', 3)

declare @param int

set @param = 1 /* 0 - All records, 1 - Only HDD, 2 - All exclude HDD */

select 
    * 
from @Accessories
where itype = @param /* NEED RIGHT CONDITION HERE*/

I can't write code like if @param = 0 then ... Is it way to write condition in WHERE statement?

Thanks!

回答1:

You could use something like this:

select * 
from @Accessories 
where (@Param = 0)
  or (@Param = 1 and atype = 'HDD') 
--or (@Param = 1 and itype = 1) , if itype and atype are connected
  or (@Param = 2 and atype != 'HDD')
option (recompile)

If Param is 0 the first condition becomes 0=0 and matches for all rows, so all rows are returned. If param is 1 or 2, only the respective or branch matches on the first condition, so it returns what the second condition specifies.

Also, the option (recompile) is really important (view the article in Martin's comment). It instructs SQL server to use the runtime values of the parameters when preparing an execution plan, so basically:

• when @Param = 0 the query becomes select * from @Accessories
• when @Param = 1 the query becomes select * from @Accessories where atype = 'HDD'
• when @Param = 2 the query becomes select * from @Accessories where atype != 'HDD'

回答2:

WHERE itype BETWEEN @param AND CASE 
    WHEN @param = 1 THEN 1
    ELSE 2000000000 END
OPTION (RECOMPILE); -- thanks for the reminder @Martin

回答3:

declare @Accessories table (id int, name nvarchar(20), atype nvarchar(20))

insert into @Accessories values (1, 'Seagate HDD 100GB', 'HDD')
insert into @Accessories values (2, 'Samsung HDD 100GB', 'HDD')
insert into @Accessories values (3, 'WD HDD 500GB', 'HDD')
insert into @Accessories values (4, 'Samsung 4GB', 'RAM')
insert into @Accessories values (5, 'GeForce 512MB', 'Video')

declare @param int

set @param = 0 /* 0 - All records, 1 - Only HDD, 2 - All exclude HDD */

if(@param =0)
begin
select * from @Accessories
end
if(@param =1)
begin
select * from @Accessories where atype in ('HDD')
end
if(@param =2)
begin
select * from @Accessories where atype not in('HDD')
end