CASE statement in where clause

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

SQL Server Development (2000)

CASE statement in where clause

Author

Topic

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-05-01 : 03:28:15

Hi all,

I hope someone know a solution to this, I can't seem to find the answer...

I have a sproc which accepts (among others) two parameters on a SQL2k database.

SupplierID INT
SupplierIDType INT

Based on the SupplierID type I need to evaluate the SupplierID.

If the SupplierIDType = 0 I need to select all the suppliers, if the SupplierIDType = 1 I need to select the supplier with the ID passed in the SupplierID. Finally if the SupplierIDType = 2 I need to select all the suppliers except the one passed in the SupplierID

I tried to use a CASE expression

WHERE S.SupplierID = CASE @SupplierIDType
WHEN 0 THEN S.SupplierID
WHEN 1 THEN @SupplierID
END

Of course this doesn't include the @SupplierIDType 2, and as said I can't figure out a way to do this. Anyone?

Peter

(BTW I posted this before but couldn't find it so posted it again, sorry if this is posted twice...)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-05-01 : 05:23:10

2 things....

1...the following link will give you a better idea of the format of a CASE statement.....do a FORUM SEARCH also on CASE....and follow some of the links that come up...I don't "think" what you have is syntactically correct...

2...I suspect dynamic SQL would be more appropriate here....(OR MAY BE MANDATORY)

ie...
if SupplierIDType = 0
set &strsql = "select x,y,z,etc from tablea"
if SupplierIDType = 1
set &strsql = "select x,y,z,etc from tablea where supplierid = " & supplierid
if SupplierIDType = 2
set &strsql = "select x,y,z,etc from tablea where supplierid <> " & supplierid
exec (&strsql)

see other examples here on this...check out the proper syntax first....this is just a quick sketch.

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-05-01 : 05:32:33

Huh?

Thanks for your reply, but the syntax for the CASE expression is good. Really nothing wrong with that. I just need to find a way to include the @SupplierIDType = 2.

What I need is

WHERE S.SupplierID CASE @SupplierIDTYpe
WHEN 0 THEN = S.SupplierID
WHEN 1 THEN = @SupplierID
WHEN 2 THEN != @SupplierID

and *that* is not proper syntax for the CASE function.

And I really don't want to use dynamic SQL if there's another solution. Of course when there's no other way I'll have to use it.

In fact I just thought of this solution which does the job. But I'm still intrested to see if there are any other solutions.

WHERE S.SupplierID = CASE @SupplierIDType
WHEN 1 THEN @SupplierID
ELSE S.SupplierID
END
AND S.SupplierID != CASE @SupplierIDType
WHEN 2 THEN @SupplierID
ELSE 0 -- there are no suppliers with ID = 0
END

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-01 : 09:15:43

You could try . . .


select
    <colmlist>
from
    sometable t
where
    exists (
        select 1
        from
            sometable s
        where
            (@SupplierIDType = 0) or
            (@SupplierIDType = 1 and
             s.SupplierID = t.SupplierID) or
            (@SupplierIDType = 2 and
             s.SupplierID <> t.SupplierID) )

Let us know which is faster . . .

<O>

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-05-02 : 02:41:10

Thank you for your reply!

I hadn't thought of that, and it works good.

I can't find any big differences in speed, but I think your solution will work faster because the CASE function doesn't have to be evaluated.

Peter

Lavos
Posting Yak Master

200 Posts

Posted - 2002-05-02 : 03:12:53

Depending on what else you have in the query, dynamic SQL _should_ be faster depending exactly on how good the optimizer is. (then again, you could use an "if else if else" block to get the same results, but I hear that that gives the optimizer fits in some situations.)

To Andrew, there are 2 different syntaxes for the Case statement. This is an example of a searched case statement, while the other syntax evaluates expressions and returns the then portion of the first expression that returns true. I don't know the name of that version off hand.

But, just because I love puzzles, heres _a_ case based solution. There are others. Another alternative is to use a case on _both_ sides of the equality operator, but that would disable the use of an index on SupplierID, which would kill the version that would seek to a specific record.

WHERE S.SupplierID = CASE @SupplierIDTYpe
WHEN 0 THEN S.SupplierID
WHEN 1 THEN @SupplierID
WHEN 2 THEN -- I dislike this approach.
CASE
WHEN @SupplierID <> S.SupplierID THEN S.SupplierID
ELSE S.SupplierID + 1 -- or some other guardian value.
END
END

Or, another approach:
WHERE S.SupplierID = CASE
WHEN @SupplierIDTYpe = 0 THEN = S.SupplierID
WHEN @SupplierIDTYpe = 1 THEN = @SupplierID
WHEN @SupplierIDTYpe = 2 AND S.SupplierID <> @SupplierID THEN = S.SupplierID
ELSE S.SupplierID + 1 -- or some other guardian.
END

You might want to try the three different solutions on a large data-set to find which is fastest in your case. I really don't think the case statement would really slow anything down opposed to a correlated subquery.

Oh yeah, there is a join solution to, but I'll leave that as an exercise to the reader :) (I think it should be faster than the subquery, but maybe not as fast as the case depending on how the optimizer expands it.)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Edited by - Lavos on 05/02/2002 03:19:22

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources