RE: Conditional Join?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hal Heinrich (HalHeinrich_at_discussions.microsoft.com)
Date: 01/10/05 

Date: Mon, 10 Jan 2005 14:31:05 -0800

The following may help:
WHERE c_language_skills = CASE WHEN @language_id = 'NA' THEN
c_language_skills ELSE @language_id END

Note that this will always exclude records where c_language_skills is null.
The following includes those:
WHERE COALESE(c_language_skills, 'NULL') = CASE WHEN @language_id = 'NA'
THEN COALESE(c_language_skills, 'NULL') ELSE @language_id END

"Brian" wrote:

> I currently have a stored proc that is dynamic. I build it up based in a
> variety of variables passed in. I would like to move away from this if
> possible.
>
> I have one sticking point, I have a series of statements like this
>
> if @language_id <> 'NA'
> BEGIN
> set @joins = @joins+' JOIN c_language_skills ls ON c.candidate_id =
> ls.candidate_id'
> set @where = @where+' AND ls.language_id IN ('+@language_id+')'
> END
>
> Is it possible to use a CASE statement or maybe COALESCE ?
>
> The join is optional because the user didn't need to query, in this case, on
> the language skills. If I used Coalesce and had Null would it query all
> the rows or ignore it?
>
> Hope that makes sense.
>
> Thanks
>
>
>

Relevant Pages

  • Re: Conditional Join?
    ... One correction on earlier post, Im not checking for NA Im actually checking ... >>Is it possible to use a CASE statement or maybe COALESCE? ... >>the language skills. ... If I used Coalesce and had Null would it query all ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL null behavior with DATE columns
    ... The IS NOT NULL is testing the date_expression which is assured by the COALESCE() in the NTE, to never be the NULL value. ... Rows where the termination date is GREATER than or EQUAL to ... Selection on an invalid date value does not allow the query engine to determine if the row should be included or omitted, since its value can not be determined. ...
    (comp.sys.ibm.as400.misc)
  • Re: Scalar Functions in Group By Clause returning invalid values
    ... Hi Mike, ... Are you aware that the function will be executed twice for each row in ... I'm afraid that your query won't scale well... ... SELECT COALESCE AS ClientType, ...
    (microsoft.public.sqlserver.programming)
  • Re: No distinct in a select into stement ?
    ... WHERE COALESCE (title1, title2, title3) IS NOT NULL ... bang goes performance too other than a probable clustered index scan ... -- Query 1 ...
    (comp.databases.ms-sqlserver)
  • Conditional Join?
    ... I currently have a stored proc that is dynamic. ... variety of variables passed in. ... Is it possible to use a CASE statement or maybe COALESCE? ... The join is optional because the user didn't need to query, in this case, on ...
    (microsoft.public.sqlserver.programming)