Selecting data based on a variable

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

Selecting data based on a variable

Author

Topic

smithani
Starting Member

42 Posts

Posted - 2007-06-07 : 16:27:56

Hi All

I want to select a certainnumber of rows

select custno, amt, balance from customer where custno='customerno'
when showcust='r' then select rows where amt<balance
when showcust='c' then amt>balance etc
if showcust='' then show everything

Any insight will be greatly apprecaited.
Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-07 : 16:41:46

Looks like you have to use dynamic SQL..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-07 : 16:44:41

it is simple boolean logic. I assume that showcust is a variable or parameter; if so, it needs to be prefixed with @.


select custno, amt, balance 
from customer 
where custno='customerno' and
  ((@showcust = 'r' and amt<balance) or
   (@showcust = 'c' and amt>balance) or
   (@showcust = ''))

- Jeff
http://weblogs.sqlteam.com/JeffS

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-07 : 16:50:37

quote:
Originally posted by dinakar

Looks like you have to use dynamic SQL..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

oops...

sorry..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

smithani
Starting Member

42 Posts

Posted - 2007-06-11 : 16:38:49

Thanks for your suggestion.Appreciate it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources