Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

help on query

Status
Not open for further replies.

tirf

Programmer
Joined
Sep 22, 2008
Messages
59
Location
US
Is there a way we can write this in one select statment instead of if ..else

Code:
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
 
Code:
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)
 
Thank you for your response
 
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...

Code:
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
 
Good point George.

Luckly in my case @i cannot be null

Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top