case statements

16 Dec 2005 7:47 PM

HP

Can you use case statements in a where clause? if so, how?

Thanks!

16 Dec 2005 8:05 PM

Mark Williams

I was doing something recently where I asked myself the same question. I
don't believe that you can use CASE directly in a WHERE clause, like

WHERE (CASE ....)

You can do multiple comparisons of a single value in the WHERE clausse, like

WHERE (value1 BETWEEN x AND y) AND (value1 NOT BETWEEN w AND z)

You could use CASE to define what you are comparing a value to in the WHERE
clause, such as

WHERE value1 IN (SELECT value2 = CASE column_name WHEN ..... FROM table)

Hope this is helpful

Show quote

"HP" wrote:

> Can you use case statements in a where clause? if so, how?
>
> Thanks!

16 Dec 2005 8:05 PM

Aaron Bertrand [SQL Server MVP]

Trivial and contrived example:

CREATE TABLE dbo.foo
(
    bar INT
);
GO

INSERT dbo.foo(bar) SELECT 1;
INSERT dbo.foo(bar) SELECT 2;
INSERT dbo.foo(bar) SELECT 3;

DECLARE @i INT;
SELECT @i = 3;

SELECT * FROM dbo.foo
    WHERE bar = CASE
        WHEN @i = 1 THEN 1
        WHEN @i = 2 THEN 2
        WHEN @i = 3 THEN 3
        ELSE bar
    END;
GO

DROP TABLE dbo.foo;

The important thing to remember is that CASE returns an expression, and that
all possible values it returns must be the same datatype (or can legally be
converted to the same datatype). A lot of people try to use CASE as an
IF/THEN/ELSE replacement, and that is not what it's for.

Please see the CASE topic in Books Online for more information.

Show quote

"HP" <H*@discussions.microsoft.com> wrote in message
news:F9B1AB78-6474-47F5-A445-D8BE079D7111@microsoft.com...
> Can you use case statements in a where clause? if so, how?
>
> Thanks!

16 Dec 2005 9:12 PM

HP

Thanks!

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> Trivial and contrived example:
>
> CREATE TABLE dbo.foo
> (
>     bar INT
> );
> GO
>
> INSERT dbo.foo(bar) SELECT 1;
> INSERT dbo.foo(bar) SELECT 2;
> INSERT dbo.foo(bar) SELECT 3;
>
> DECLARE @i INT;
> SELECT @i = 3;
>
> SELECT * FROM dbo.foo
>     WHERE bar = CASE
>         WHEN @i = 1 THEN 1
>         WHEN @i = 2 THEN 2
>         WHEN @i = 3 THEN 3
>         ELSE bar
>     END;
> GO
>
> DROP TABLE dbo.foo;
>
> The important thing to remember is that CASE returns an expression, and that
> all possible values it returns must be the same datatype (or can legally be
> converted to the same datatype). A lot of people try to use CASE as an
> IF/THEN/ELSE replacement, and that is not what it's for.
>
> Please see the CASE topic in Books Online for more information.
>
>
>
>
>
>
>
> "HP" <H*@discussions.microsoft.com> wrote in message
> news:F9B1AB78-6474-47F5-A445-D8BE079D7111@microsoft.com...
> > Can you use case statements in a where clause? if so, how?
> >
> > Thanks!
>
>
>