How to make shorter of one statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to make shorter of one statement?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2007-07-27 : 14:17:53

I code a statement like below:

if Name = 'AAA1' or Name = 'AAA2' or Name = 'AAA3' or Name = 'AAA4' or Name = 'AAA5' or Name = 'AAA6' or Name = 'AAA7' or Name = 'AAA8' then Order = 100

How to make it shorter?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-27 : 14:21:35

NAME IN ('AAA1' , 'AAA2' , 'AAA3' , 'AAA4' , 'AAA5' , 'AAA6' , 'AAA7' , 'AAA8' )

Is that short enough

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

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2007-07-27 : 15:14:06

I tried it but IN statement only work in

Select name in ('AAA1' , 'AAA2' , 'AAA3' , 'AAA4' , 'AAA5' , 'AAA6' , 'AAA7', 'AAA8')
...

But below is not working:

if NAME IN ('AAA1' , 'AAA2' , 'AAA3' , 'AAA4' , 'AAA5' , 'AAA6' , 'AAA7' , 'AAA8' ) then...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 15:29:24

If using IF you need a variable or EXISTS

IF EXISTS (SELECT * FROM Table1 WHERE Name IN (....))

And remember, THEN is not used together with IF in SQL Server.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 15:30:27

quote:
Originally posted by dinakar

NAME IN ('AAA1' , 'AAA2' , 'AAA3' , 'AAA4' , 'AAA5' , 'AAA6' , 'AAA7' , 'AAA8' )

Is that short enough

This is shorter

Name LIKE 'AAA[1-8]'

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 16:01:18

SELECT CASE WHEN Name LIKE 'AAA[1-8]' THEN 100
ELSE ??? END AS Order
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-27 : 16:01:43

quote:
Originally posted by Peso

quote:
Originally posted by dinakar

NAME IN ('AAA1' , 'AAA2' , 'AAA3' , 'AAA4' , 'AAA5' , 'AAA6' , 'AAA7' , 'AAA8' )

Is that short enough
This is shorter

Name LIKE 'AAA[1-8]'

E 12°55'05.25"
N 56°04'39.16"

Indeed...

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources