Posted by Ed Murphy on 10/13/06 02:11

Dan Guzman wrote:

> 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'

Another alternative:

WHERE (NOT(@myCompany = 933) OR tblClient.companycode = 933)
AND (NOT(@myCompany <> 933) OR tblCase.clientcode = @myClient)
AND tblCase.status = 'Active'

• Next in forum: Using trigger to notify an assembly of changes.
• Prev in forum: Re: Case Statement in Where Clause
• Thread view: Re: Case Statement in Where Clause

[Reply to this message]