Conditional Select statement - SQL Server Forums

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/

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

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/

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

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/

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

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/

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))

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources