Conditional Where

I have a stored proc that accepts several parameter, one being
relocation_id. If it is passed in I want to add it to the Where clase
AND rd.relocateID = @.relocation_id
Ive been trying to avoid dynamic sql and thought I could do something with
COALESCE but I cant seem to get same results for total if I left off the
AND
So if I run my query with the AND included from above I get 238,473 records.
If I do:
AND rd.relocateID = COALESCE(@.relocation_id, rd.relocateID) I get
207566. At this point @.relocation_id is NULL because it wasnt past in.
Im not even sure this is possible or are there other paths?
Thanks!is rd.relocateID nullable?|||The below takes advantage of @.relocation_id being NULL if it is not passed t
o
the stored procedure. If you have a default value, you will need to modify
accordingly.
SELECT [SomeColumns] FROM [SomeTable]
WHERE [OtherConditions]
AND CASE
WHEN @.relocation_id IS NOT NULL AND rd.relocateid = @.relocation_id THEN 1
WHEN @.relocation_id IS NULL THEN 1
ELSE 0
END = 1
"Brian" wrote:

> I have a stored proc that accepts several parameter, one being
> relocation_id. If it is passed in I want to add it to the Where clase
> AND rd.relocateID = @.relocation_id
> Ive been trying to avoid dynamic sql and thought I could do something with
> COALESCE but I cant seem to get same results for total if I left off the
> AND
> So if I run my query with the AND included from above I get 238,473 record
s.
> If I do:
> AND rd.relocateID = COALESCE(@.relocation_id, rd.relocateID) I get
> 207566. At this point @.relocation_id is NULL because it wasnt past in.
> Im not even sure this is possible or are there other paths?
> Thanks!
>
>
>|||That worked perfect. Thanks. One follow up and not this is possible
I am taking the query that was in a Coldfusion page and making it an SP and
it was a conditional join
<cfif len(arg.degreeID)>LEFT OUTER JOIN resume_education ed ON ( rd.userID =
ed.userID )</cfif>
So if on the search page someone picked a Degree we would then need to join
in that table as well. Is the thinking here you should
just always make the join and then have the where clause be conditional like
below.
Thanks again!
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:6DAC0EEE-E8F0-4549-9A22-E3CD91D671E4@.microsoft.com...
> The below takes advantage of @.relocation_id being NULL if it is not passed
> to
> the stored procedure. If you have a default value, you will need to modify
> accordingly.
> SELECT [SomeColumns] FROM [SomeTable]
> WHERE [OtherConditions]
> AND CASE
> WHEN @.relocation_id IS NOT NULL AND rd.relocateid = @.relocation_id THEN 1
> WHEN @.relocation_id IS NULL THEN 1
> ELSE 0
> END = 1
>
> --
>
> "Brian" wrote:
>