Building a conditional WHERE clause

Hello experts, I have a sproc that gets several params, default is null. I'
d
like to build a WHERE clause on the fly for just the params with values.
Something like
SELECT * from foo
WHERE if( @.a IS NOT NULL foo.a = @.a) and if( @.b IS NOT NULL foo.b = @.b)IF is a control flow statement... You cannot use it in a query.
How about :
WHERE a = COALESCE(@.a, a)
AND b = COALESCE(@.b, b)
You can also use CASE but it's a bit more drawn out:
WHERE a = CASE WHEN @.a IS NOT NULL THEN @.a ELSE a END
AND b = CASE WHEN @.b IS NOT NULL THEN @.b ELSE b END
(And the latter will almost certainly lead to scans rather than ss, if a
and/or be is indexed.)
A
On 3/5/05 12:45 PM, in article
3AD07FB0-A316-4ED0-A29D-EB3E5F06499A@.microsoft.com, "Coffee guy"
<Coffeeguy@.discussions.microsoft.com> wrote:

> Hello experts, I have a sproc that gets several params, default is null.
I'd
> like to build a WHERE clause on the fly for just the params with values.
> Something like
> SELECT * from foo
> WHERE if( @.a IS NOT NULL foo.a = @.a) and if( @.b IS NOT NULL foo.b = @.b)
>
>|||Aaaah, thanks!
"Aaron [SQL Server MVP]" wrote:

> IF is a control flow statement... You cannot use it in a query.
> How about :
> WHERE a = COALESCE(@.a, a)
> AND b = COALESCE(@.b, b)
> You can also use CASE but it's a bit more drawn out:
> WHERE a = CASE WHEN @.a IS NOT NULL THEN @.a ELSE a END
> AND b = CASE WHEN @.b IS NOT NULL THEN @.b ELSE b END
> (And the latter will almost certainly lead to scans rather than ss, if
a
> and/or be is indexed.)
> A
>
> On 3/5/05 12:45 PM, in article
> 3AD07FB0-A316-4ED0-A29D-EB3E5F06499A@.microsoft.com, "Coffee guy"
> <Coffeeguy@.discussions.microsoft.com> wrote:
>
>