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 2000 Forums

Transact-SQL (2000)

CASE statement

Author

Topic

Rocko
Starting Member

26 Posts

Posted - 2006-05-22 : 10:12:28

Hello,

I was wondering if somebody can help with the CASE statement below (Not working). I’m not sure if it’s possible to do it that way in store procedure.

……
Where AND Status = 'Completed'
(case when @groupID > 0 then
(AND Code in (select code from contact where groupid = @groupID)
else AND Code = @Code) end)
AND i.OrderID like '%' + @OrderID + '%'
………

Thanks a lot!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-22 : 10:19:57

Where Status = 'Completed'
and xxxx = (case when @groupID > 0 then
case when Code in (select code from contact where groupid = @groupID)
then ...
else ...
end
else ...
end

a case statement just returns a value and can be nested.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Rocko
Starting Member

26 Posts

Posted - 2006-05-22 : 11:18:55

Hi nr

Thanks for that. However my select statement is little different. It’s either @groupID more then 0 or if not then will be code. In other words I would like to have something like

Where Status = 'Completed'
Case 
 when @groupID > 0 then
   AND Code in (select code from contact where groupid = @groupID
 Else
   AND Code = @Code
END

I want to have the expression embedded into the case statement rather then having two case statements. This is because in the first expression I have ‘in’ and in the second ‘=’. So how should look like the syntax for that?

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-22 : 11:40:58

What you have doesn't make sense.
As I said a case statement retuns a value.

Where Status = 'Completed'
Case
end

is not possible - it's like
Where Status = 'Completed'
'x'

you need a boolean returned i.e.
Where Status = 'Completed'
and 'x' = case ... end

also
Case
when @groupID > 0 then <must have the returned value here>

Case
when @groupID > 0 then and
again doesn't make sense.

Try looking at what you are trying to do - maybe write it down in english then translate it into the where clause - it might be that a case statement isn't what you want.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Rocko
Starting Member

26 Posts

Posted - 2006-05-22 : 13:36:44

Hi,

Let me explain. I have a table contact where I have contact information. Based on contact name will display the associated data. But some users (super users) can see data from other users if they have groupID in ContactGroup table. Every user can see its own data. Some of the users (super users) will be able to see shared data only if they have groupID. The supers users could have more than one groupID and that’s why I have to use ‘in’ instead of ‘=’.

So I may need something like.

Where Status = 'Completed'
AND
   --the situation where a contact is a part of shared group and display shared data
   Case when groupID > 0 then 
        ContactName in (select contactName from contactGroup where groupid = @groupId)
Else
   -- the situation where is not – will display only their data
   contactName = @contactName
End

Right now this is done by two select statements. My though is that I should be able to combine them with case statement. But I’m not sure if it possible.
Let me know if I have to explain more details.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-22 : 14:08:58

Where Status = 'Completed'
AND
(
(@groupid > 0 and Code in (select code from contact where groupid = @groupID))
or
Code = @Code
)

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-05-22 : 14:20:54

Like nr mentions, case just returns a value, it is not used for branching like some other languages... so you couldn't have the where clause branch like that, but depending on the actual db structure, you could get similar results by changing your query a bit..

if every user was in at least one group (so every contactName was somewhere in the contactGroup table), and groupid of 0 indicates a not superuser, you could do something like:


where Status = 'Completed'
AND ContactName IN
(   SELECT distinct contactName
    FROM contactGroup
    WHERE contactName = @contactName
    OR groupid = @groupID
)

if the @groupID is 0 then the second half of the OR does nothing and you just get the match on the @contactName...

hmm need more coffee..

Rocko
Starting Member

26 Posts

Posted - 2006-05-22 : 14:50:17

Yes that’s make sense. I was probably confused with case statement with no need to use it in that scenario.
Thanks Friends!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources