CASE WHEN EXISTS - 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

CASE WHEN EXISTS

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-15 : 11:35:59

I'm trying to write a sub select which i need to return a 1 if userid = -1 and 0 for anything else. The name of the column should be changed to isPublic. I'm trying to use CASE but it doesn't allow userid as isPublic..My code below is not quite working


SELECT CASE 
     WHEN EXISTS 
(SELECT userid FROM UserPermission WHERE itemid = @HItemID and userid = -1)
THEN 1
ELSE 0

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-15 : 11:41:40

add to your posted statement:
END AS isPublic,
other columns...
from ...

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-15 : 12:11:49

I did not know you could do CASE WHEN EXISTS (....

Not the I EVER had the need to do it that way....

and with (NOLOCK) no less

You like to get "Dirty"

???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-15 : 12:15:29

quote:
Originally posted by X002548

I did not know you could do CASE WHEN EXISTS (....

Not the I EVER had the need to do it that way....

and with (NOLOCK) no less

You like to get "Dirty"

???

Brett

8-)

:) yeah well I'm just being consistent with similar code in other SP's
By the way I messed up on the data so actually its now doing what I'm expecting so I've deleted the last reply.

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-15 : 12:26:18

well maybe it's time to "draw a line in the sand" (to quote a terrorist) and start coding correctly..

MOO

And don't use SELECT *

Except in Sub queries

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 12:30:52

"I did not know you could do CASE WHEN EXISTS (...."

CASE WHEN expression

isn't it?

Or am I missing some gem of information - you usually have some deep-and-meaningful thought behind these comments!

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-15 : 13:04:04

Did you EVER do a SELECT against a table in a CASE Statement?

Usually that is done either via Join Predicates or regular predicates

LIKE

SELECT ....

FROM ....

WHERE UserID IN
(SELECT userid FROM UserPermission
WHERE itemid = @HItemID and userid = -1)

And what, this is a result set?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 13:11:00

"And what, this is a result set?"

Ah, now I see. I knew it would be worth asking

I assumed the SELECT CASE ... was just a result column and the FROM was just not displayed in the Post for the sake of making a short example for the forum.

But there is no correlated column in the CASE, so you-are-right-Father-William (and I'm the Old Man!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources