help on query

[tirf]

Is there a way we can write this in one select statment instead of if ..else

declare @i int
set @i = 7

if @i = 1
select * from Production.Product where ProductID in (2,3,4)
else 
select * from Production.Product where ProductID in (316, 317, 318)

Thanks

 

[RiverGuy]

declare @i int
set @i = 7


select * from Production.Product where (ProductID in (2,3,4) AND @i = 1) OR  (ProductID in (316, 317, 318) AND @i <> 1)

 

[tirf]

Thank you for your response

 

[gmmastros]

If @i cannot be NULL, then RiverGuy's suggestion should work well. If it can be null, the ELSE part would be executed from the original post. With RiverGuy's query, you wouldn't get any rows returned.

For example...

Declare @Temp Table(ProductId Int, Data VarChar(10))

Insert Into @Temp Values(2, 'red')
Insert Into @Temp Values(3, 'blue')
Insert Into @Temp Values(316, 'green')

Declare @i Int

Set @i = 1

select * from @Temp
where (ProductID in (2,3,4) AND @i = 1) OR  (ProductID in (316, 317, 318) AND @i <> 1)

Set @i = 7

select * from @Temp
where (ProductID in (2,3,4) AND @i = 1) OR  (ProductID in (316, 317, 318) AND @i <> 1)

Set @i = NULL
select * from @Temp
where (ProductID in (2,3,4) AND @i = 1) OR  (ProductID in (316, 317, 318) AND @i <> 1)

Notice that the last query doesn't return any rows. I'm not saying this is a problem, but it might be.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tirf]

Good point George.

Luckly in my case @i cannot be null

Thanks