CASE 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

SQL Server 2005 Forums

Transact-SQL (2005)

CASE statement

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2007-09-27 : 12:43:29

I haven't done SQL queries for about 2 years now and it's getting rusty. Will someone please help me with this?

The user enters either an email address or a group name. With this input value, I need to determine if the groupname field is NULL or NOT NULL. An email address would have groupname is NULL.

I then would return the email if groupname is NULL or return the group name if groupname is NOT NULL.

Thanks.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 12:48:18

Not sure about the performance but you can do this:


SELECT * FROM YourTable
WHERE (Case WHEN GroupName IS NULL THEN Email ELSE GroupName END) = @Value

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

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 12:49:51

The crude way will be :


IF EXISTS (SELECT * FROM YourTable WHERE GroupName = @Value)
   BEGIN

   END
ELSE IF EXISTS (SELECT * FROM YourTable WHERE Email = @Value)

   BEGIN

   END

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

PGG123
Yak Posting Veteran

55 Posts

Posted - 2007-09-27 : 12:50:31

Thanks, that was quick. But how do I limit my select clause to return either email or groupname?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 12:55:11

You can either get both into variables and check for null or use the second method I posted and select the particular column.

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

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 13:20:22

If I've got the right end of the stick it should be:


SELECT COALESCE(GroupName, Email) AS [MyColumnName]
FROM YourTable
WHERE GroupName = @Value
      OR (GroupName IS NULL AND Email = @Value)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources