Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2003
    Location
    France
    Posts
    21

    Unhappy Unanswered: optional critera

    I have a SP with many parameters. If not supplied, parameter default value is NULL.

    The parameters are to be used in the WHERE clause:

    ALTER PROCEDURE dbo.SearchFlight

    (
    @DepartureDate datetime = null,
    @ReturnDate datetime= null,
    @MaxPrice money= null,
    @Country int= null,

    )

    AS
    SELECT *
    FROM myTables with joint
    WHERE
    DepartureDate = @DepartureDate
    AND ReturnDate = @ReturnDate
    AND MaxPrice = @MaxPrice
    AND Country = @Country

    If a parameter is null, I dont want it to be in the WHERE clause.
    I dont want to use a string parameter and execute a "string" select.
    I dont want to use a combination of IF to check everything ...

    Is ther a solution ? using Case statement ???

    Thanks for your help

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Two methods:

    WHERE
    (DepartureDate = @DepartureDate or @DepartureDate is null)
    AND (ReturnDate = @ReturnDate or @ReturnDate is null)
    AND (MaxPrice = @MaxPrice or @MaxPrice is null)
    AND (Country = @Country or @Country is null)


    WHERE
    DepartureDate = isnull(@DepartureDate , DepartureDate)
    AND ReturnDate = isnull(@ReturnDate, ReturnDate)
    AND MaxPrice = isnull(@MaxPrice, MaxPrice)
    AND Country = isnull(@Country, Country)

    ...but neither of these is very efficient. In cases such as this, dynamic SQL is frequently much faster.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Uh-oh...Did I hear you right? It's probably a rough morning, not that you really think that way...Or maybe it's somebody that came up to your PC while you went to the batthroom and forgot to lock your workstation...I think that's it, then never mind
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    Uh-oh...Did I hear you right? It's probably a rough morning, not that you really think that way...Or maybe it's somebody that came up to your PC while you went to the batthroom and forgot to lock your workstation...I think that's it, then never mind
    Only one doing a seek in this case is dynamic...

    Code:
    USE Northwind
    GO
    
    DECLARE @OrderDate datetime, @RequiredDate datetime, @ShippedDate datetime, @SQL varchar(8000)
    
    SELECT @OrderDate = '1996/08/14', @RequiredDate = NULL, @ShippedDate = NULL
    
    SELECT * FROM Orders
    WHERE (OrderDate    = @OrderDate    or @OrderDate    is null)
      AND (RequiredDate = @RequiredDate or @RequiredDate is null)
      AND (ShippedDate  = @ShippedDate  or @ShippedDate  is null)
    
    SELECT * FROM Orders
    WHERE OrderDate    = ISNULL(@OrderDate,OrderDate)
      AND RequiredDate = ISNULL(@RequiredDate,RequiredDate)
      AND ShippedDate  = ISNULL(@ShippedDate,ShippedDate)
    
    SELECT @SQL = 'SELECT * FROM Orders WHERE 1 = 1 '
    IF @OrderDate    IS NOT NULL 
      SELECT @SQL = @SQL + 'AND OrderDate = '    + '''' + CONVERT(varchar(10),@OrderDate,101)    + '''' + ' '
    IF @RequiredDate IS NOT NULL 
      SELECT @SQL = @SQL + 'AND RequiredDate = ' + '''' + CONVERT(varchar(10),@RequiredDate,101) + '''' + ' '
    IF @ShippedDate  IS NOT NULL 
      SELECT @SQL = @SQL + 'AND ShippedDate = '  + '''' + CONVERT(varchar(10),@ShippedDate,101)  + '''' + ' '
    
    EXEC(@SQL)
    But why code it that way?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well this was an expensive waste of time

    Code:
    SELECT @OrderDate    AS OrderDate    INTO #OrderDateTemp 
    SELECT @RequiredDate AS RequiredDate INTO #RequiredDateTemp 
    SELECT @ShippedDate  AS ShippedDate  INTO #ShippedDateTemp 
    
    SELECT * 
    FROM Orders o
    LEFT JOIN #OrderDateTemp    ot ON o.OrderDate    = ot.OrderDate 
    LEFT JOIN #RequiredDateTemp rt ON o.RequiredDate = rt.RequiredDate 
    LEFT JOIN #ShippedDateTemp  st ON o.ShippedDate  = st.ShippedDate 
    WHERE ot.OrderDate    IS NOT NULL
       OR rt.RequiredDate IS NOT NULL
       OR st.ShippedDate  IS NOT NULL
    
    DROP TABLE #OrderDateTemp 
    DROP TABLE #RequiredDateTemp 
    DROP TABLE #ShippedDateTemp
    It sets up an index scan on Orders then does a hash right join to the temps...

    I thought that would scan the temps...no big deal..1 row, then index seek the orders...damn...

    that should work...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, this is more for an OR condition...but I got the INDEX SEEK I wanted...

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    
    DECLARE @OrderDate datetime, @RequiredDate datetime, @ShippedDate datetime, @SQL varchar(8000)
    
    SELECT @OrderDate = '1996/08/14', @RequiredDate = NULL, @ShippedDate = NULL
    
    CREATE INDEX Orders_RequiredDate ON Orders(RequiredDate)
    
    SELECT @OrderDate    AS OrderDate    INTO #OrderDateTemp 
    SELECT @RequiredDate AS RequiredDate INTO #RequiredDateTemp 
    SELECT @ShippedDate  AS ShippedDate  INTO #ShippedDateTemp 
    
    SELECT * 
    FROM Orders o
    INNER JOIN #OrderDateTemp    ot ON o.OrderDate    = ot.OrderDate
    UNION ALL 
    SELECT * 
    FROM Orders o
    INNER JOIN #RequiredDateTemp rt ON o.RequiredDate = rt.RequiredDate
    UNION ALL
    SELECT * 
    FROM Orders o
    INNER JOIN #ShippedDateTemp  st ON o.ShippedDate  = st.ShippedDate 
    
    DROP INDEX Orders.Orders_RequiredDate
    DROP TABLE #OrderDateTemp 
    DROP TABLE #RequiredDateTemp 
    DROP TABLE #ShippedDateTemp 
    
    SET NOCOUNT OFF
    GO
    Wonder why the other one scans though....

    pity
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Brett,

    You were on the right track with UNION ALL. What I noticed though was that Orders table had only 2 out of 3 date fields indexed. RequiredDate was not indexed. So I played with it, even changed the SELECT to include a couple of other fields (OrderID, CustomerID, EmployeeID) along with the date fields, and even created a covered index that included all 6 fields...Still was getting Index Scan...until...I looked at your UNION ALL...and...

    In other words, you don't even need a temp table, just 3 individual indexes on each field in the OR list, UNION ALLed, and you get an Index Seek on all 3 SELECTs!!!

    Code:
    SELECT *
    FROM Orders o
    where o.OrderDate     = @OrderDate
    union all
    SELECT *
    FROM Orders o
    where o.RequiredDate  = @RequiredDate
    union all
    SELECT *
    FROM Orders o
    where o.ShippedDate   = @ShippedDate
    Sounds very simple, but that is (I believe) an alternative to dynamic SQL that everybody seems to resort to. Of course I would not recommend to use this technique on low selectivity fields, because the overall cost of such a query (providing we're talking about large recordsets) will be enormous.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, I noticed the missing index...I added it in my code...

    And yes I got Index seeks...

    But that's still a set of OR's, not AND's....

    In other words JOINS are AND'S and UNIONS are OR's

    Agree?

    I though the LEFT JOINS would be ANDS, and if it's NULL, then it would be non existant and wouldn't be in play...effectivley taking it out of the "WHERE" condition...

    Oh and for the Joins I did add the Index...

    I wonder if it's the NULL Condition...

    Nah thinking while I'm typing, it's the outter join...it has no joice but to do an index scan...
    Last edited by Brett Kaiser; 05-05-04 at 15:53.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    GOT IT

    All Index Seeks on the main table....

    Just add as many Left joins as you need, and make sure there's an index on each...

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    
    
    CREATE INDEX Orders_RequiredDate ON Orders(RequiredDate)
    
    DECLARE @OrderDate datetime, @RequiredDate datetime, @ShippedDate datetime, @SQL varchar(8000)
    
    SELECT @OrderDate = '1996/08/14', @RequiredDate = NULL, @ShippedDate = NULL
    
    SELECT @OrderDate    AS OrderDate    INTO #OrderDateTemp 
    SELECT @RequiredDate AS RequiredDate INTO #RequiredDateTemp 
    SELECT @ShippedDate  AS ShippedDate  INTO #ShippedDateTemp 
    
        SELECT * 
          FROM Orders o
     LEFT JOIN (	    SELECT OrderId FROM #OrderDateTemp ot    
    		INNER JOIN Orders o1  ON o1.OrderDate = ot.OrderDate) AS ot 
    	ON o.OrderId = ot.OrderId
     LEFT JOIN
    	  (	    SELECT OrderId FROM #RequiredDateTemp rt 
    		INNER JOIN Orders o1  ON o1.RequiredDate = rt.RequiredDate) AS rt
    	ON o.OrderId = rt.OrderId
     LEFT JOIN
    	  (	    SELECT OrderId FROM #ShippedDateTemp st  
    		INNER JOIN Orders o1  ON o1.ShippedDate = st.ShippedDate) AS st
    	ON o.OrderId = rt.OrderId
         WHERE ot.OrderId IS NOT NULL
            OR rt.OrderId IS NOT NULL
            OR st.OrderId IS NOT NULL
    
    DROP INDEX Orders.Orders_RequiredDate
    DROP TABLE #OrderDateTemp 
    DROP TABLE #RequiredDateTemp 
    DROP TABLE #ShippedDateTemp 
    
    SET NOCOUNT OFF
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Another flavor:

    WHERE
    Nullif(@DepartureDate , DepartureDate) is null
    AND Nullif(@ReturnDate, ReturnDate) is null
    AND Nullif(@MaxPrice, MaxPrice) is null
    AND Nullif(@Country, Country) is null
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I still get an index scan

    Code:
    DECLARE @OrderDate datetime, @RequiredDate datetime, @ShippedDate datetime, @SQL varchar(8000)
    
    SELECT @OrderDate = '1996/08/14', @RequiredDate = NULL, @ShippedDate = NULL
    
    CREATE INDEX Orders_RequiredDate ON Orders(RequiredDate)
    
    SELECT * FROM Orders
    WHERE Nullif(@OrderDate,    OrderDate) is null
      AND Nullif(@RequiredDate, RequiredDate)  is null
      AND Nullif(@ShippedDate,  ShippedDate)   is null
    
    DROP INDEX Orders.Orders_RequiredDate
    GO
    Clever to think about in reverse though...

    Sorry for the fluff Rudy

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •