Conditional Join?

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

From: Brian (brian_at_nospam.com)
Date: 01/10/05

  • Next message: Kalen Delaney: "Re: Composite Key History Table" 
    Date: Mon, 10 Jan 2005 16:42:19 -0500

    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

  • Next message: Kalen Delaney: "Re: Composite Key History Table"

    Relevant Pages

    • Re: Conditional Join?
      ... >variety of variables passed in. ... >I have one sticking point, I have a series of statements like this ... >Is it possible to use a CASE statement or maybe COALESCE? ... If I used Coalesce and had Null would it query all ...
      (microsoft.public.sqlserver.programming)
    • Re: Dynamic use of Access query
      ... > But this code needs to add parameters from the query: ... > Set adoCmd = New ADODB.Command ... EXECUTE (this is what the ADO Command object is using under the hood ... If you are saying your stored proc doesn't have any parameters, ...
      (microsoft.public.excel.programming)
    • Re: stored proc RETURN_VALUE.... there it is, I want to use it !!!
      ... > Then you can read the value of @total after closing the query. ... > 2 Set up your input param values. ... the result of this sql code returns an integer that is ... >> take the guts of the stored proc with hard coded guestid and meeting ...
      (borland.public.delphi.database.ado)
    • Re: Stored procedure never finishes
      ... Perhaps when you run the query without the date range, ... You do this by issuing "exec sp_lock [spid]" with spid being ... SQL Server MVP ... > I have a stored proc that won't finish. ...
      (microsoft.public.sqlserver.server)
    • 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)