Posted by Dan Guzman on 10/13/06 00:54

In SQL Server, CASE doesn't conditionally execute statements, it returns an
expression. You might try something like:

WHERE
tblClient.companycode =
CASE
WHEN @myCompany = 933
THEN 933 ELSE tblClient.companycode END
AND
tblCase.clientcode =
CASE
WHEN @myCompany <> 933
THEN @myClient ELSE tblCase.clientcode END
AND tblCase.status = 'Active'

--
Hope this helps.

Dan Guzman
SQL Server MVP
<paulmac106@gmail.com> wrote in message
news:1160689816.017843.61740@i3g2000cwc.googlegroups.com...
> If you could help me with my syntax i would really appreciate
> it. I'm trying to change the where clause on the fly, but it's
> giving me a syntax error.
>
> Hopefully from what I have below you can tell what i'm after
>
> the first part should evaluate a boolean expression, then if true,
> search using one field, otherwise
> search using a different field
>
> WHERE
>
> Case WHEN @myCompany = 933
> THEN tblClient.companycode = 933 --problem line
> ELSE
> tblCase.clientcode = @myClient --problem line
> END
>
> And tblCase.status = 'Active'
>
> thank you!!
>

• Next in forum: Re: Case Statement in Where Clause
• Prev in forum: Re: clever date to find most recent .bak file
• Thread view: Re: Case Statement in Where Clause

[Reply to this message]