Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Conditional Select statement

Author  Topic 

engcanada
Starting Member

39 Posts

Posted - 2015-03-04 : 12:25:58
I need help with the correct statement the following:

select * from mytable where
IF @myvariable = 0 THEN column=1 or column=2 END
IF @myvariable = 1 THEN column=1 END
IF @myvariable = 2 THEN column=2 END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 12:27:54
You can use CASE in the WHERE clause.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

engcanada
Starting Member

39 Posts

Posted - 2015-03-04 : 12:37:00
I did use it but I can't get it to work for me. Could you please show me with my statement.
Thank you
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 12:50:30
Please show us what you have so far.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

engcanada
Starting Member

39 Posts

Posted - 2015-03-04 : 14:09:47
This is what I want to do:
select * from mytable where
IF @myvariable = 0 THEN column=1 or column=2 END and
IF @myvariable = 1 THEN column=1 END and
IF @myvariable = 2 THEN column=2 END
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 14:23:16
Actually instead of CASE, just use AND/OR with the appropriate parenthesis. Here's an example:

WHERE (@var1 = 9 AND (colA = 'somestring' OR colB = 10)) AND (@var2 = 999 AND colW = 1)

I'm not clear if you want AND or OR between them. I believe you need OR, but your IFs have AND. I'd need to see some sample data for it to be clearer.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

engcanada
Starting Member

39 Posts

Posted - 2015-03-04 : 15:00:02
This did not do it.
I have a SP that receives the following @VAR = 0 or @VAR = 1 or @VAR = 2
Therefore the SELECT statement must show all rows as follows:
if @var=0 display all rows where column id = 0 or 1
HOWEVER
if @var=1 display ONLY rows where column id = 0
if @var=2 display ONLY rows where column id = 1
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 15:36:13
From my previous reply:

"I'm not clear if you want AND or OR between them. I believe you need OR, but your IFs have AND. I'd need to see some sample data for it to be clearer."

WHERE (@var=0 AND columnid IN (0,1)) OR (@var=1 AND columnid=0) OR...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

engcanada
Starting Member

39 Posts

Posted - 2015-03-04 : 16:11:58
Thank you very much!
This was the area that made it work:
columnid IN (0,1))
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 16:14:08


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -