none
Select statement with two parameters, one with higher priority

    Question

  • I need help with where block of a statement with two parameters, @Item and @ItemList,

    table.item in @ItemList (When @itemList and @item are both not null, ignor @item value)

    table.item = @item (if only @itemList is null)

    table.item in @itemlist (if only @item is null)

    return all when both are null.

    Below is similar to what I have, but it won't handle the condition when both @item and @itemlist has value. It will only take the @item.

    Function_CommaSeperateToString is a funtion that will breaks a string of items with comma into individual items.

    SELECT * FROM TABLE WHERE

    (@item IS NULL OR table.item = @item)

    AND

    (@itemList IS NULL OR table.item IN (SELECT VarValue FROM Function_CommaSeperateToString(@ItemList))

    • Edited by ASP808 Monday, March 29, 2010 7:12 PM
    Saturday, March 27, 2010 4:02 AM

Answers

  • Check out the following T-SQL logic. Let us know if works.

    NOTE: as a front-end developer, I would not code it to allow the user to enter both item and itemlist as search criteria. It is confusing.

    DECLARE @item varchar(128), @itemlist varchar(1024)
    SELECT * 
    FROM   AdventureWorks2008.Production.Product 
    WHERE  (@item IS NULL 
             OR (@itemList IS NULL 
                 AND ProductNumber = @item) 
                 OR (@item is not null and @itemlist is not null)) 
           AND (@itemList IS NULL 
                 OR ProductNumber 
                 IN (SELECT StringLiteral 
                                   FROM   dbo.fnSplitStringListXML(@ItemList,','))) 
    GO
    -- (504 row(s) affected)
    DECLARE @item varchar(128)='FR-T98Y-60', @itemlist varchar(1024)
    SELECT * 
    FROM   AdventureWorks2008.Production.Product 
    WHERE  (@item IS NULL 
             OR (@itemList IS NULL 
                 AND ProductNumber = @item) 
                 OR (@item is not null and @itemlist is not null)) 
           AND (@itemList IS NULL 
                 OR ProductNumber 
                 IN (SELECT StringLiteral 
                                   FROM   dbo.fnSplitStringListXML(@ItemList,',')))
    GO
    /*
    ProductID	Name	ProductNumber
    885	HL Touring Frame - Yellow, 60	FR-T98Y-60
    */
    DECLARE @item varchar(128), @itemlist varchar(1024)='BK-R89B-58,BK-R64Y-38,BK-R64Y-40'
    SELECT * 
    FROM   AdventureWorks2008.Production.Product 
    WHERE  (@item IS NULL 
             OR (@itemList IS NULL 
                 AND ProductNumber = @item)
                 OR (@item is not null and @itemlist is not null)) 
           AND (@itemList IS NULL 
                 OR ProductNumber 
                 IN (SELECT StringLiteral 
                                   FROM   dbo.fnSplitStringListXML(@ItemList,',')))
    GO
    /*
    ProductID	Name	ProductNumber
    796	Road-250 Black, 58	BK-R89B-58
    797	Road-550-W Yellow, 38	BK-R64Y-38
    798	Road-550-W Yellow, 40	BK-R64Y-40
    */
    
    DECLARE @item varchar(128)='FR-T98Y-60', 
            @itemlist varchar(1024)='BK-R89B-58,BK-R64Y-38,BK-R64Y-40'
    SELECT * 
    FROM   AdventureWorks2008.Production.Product 
    WHERE  (@item IS NULL 
             OR (@itemList IS NULL 
                 AND ProductNumber = @item) 
                 OR (@item is not null and @itemlist is not null)) 
           AND (@itemList IS NULL 
                 OR ProductNumber 
                 IN (SELECT StringLiteral 
                                   FROM   dbo.fnSplitStringListXML(@ItemList,',')))
    GO
    /*
    ProductID	Name	ProductNumber
    796	Road-250 Black, 58	BK-R89B-58
    797	Road-550-W Yellow, 38	BK-R64Y-38
    798	Road-550-W Yellow, 40	BK-R64Y-40
    */
    

    Link for string splitter table-valued function:

    http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

    • Marked as answer by ASP808 Tuesday, March 30, 2010 2:19 AM
    Monday, March 29, 2010 10:15 PM

All replies

  • How about?

    SELECT * 
    FROM   [TABLE] 
    WHERE  (@item IS NULL 
             OR (@itemList IS NULL 
                 AND table.item = @item)) 
           AND (@itemList IS NULL 
                 OR table.item IN (SELECT VarValue 
                                   FROM   Function_CommaSeperateToString(@ItemList))) 

    Kalman Toth, SQL Server & Business Intelligence Training; http://www.SQLUSA.com
    • Proposed as answer by Melissa Suciadi Monday, March 29, 2010 3:47 AM
    • Marked as answer by ASP808 Monday, March 29, 2010 6:54 PM
    • Unmarked as answer by ASP808 Monday, March 29, 2010 6:56 PM
    Saturday, March 27, 2010 6:43 AM
  • Thank you sir. I just noticed that when both item and itemList are not null, it returns no results. I am trying to code it so that one parameter has a higher priority over the other parameter or take both 

    Scenario 1: if both are not null, table.item in @itemList

    Scenario 2: if both are not null, table.item in @itemList + @item

    Please advise.

    Monday, March 29, 2010 7:02 PM
  • Check out the following T-SQL logic. Let us know if works.

    NOTE: as a front-end developer, I would not code it to allow the user to enter both item and itemlist as search criteria. It is confusing.

    DECLARE @item varchar(128), @itemlist varchar(1024)
    SELECT * 
    FROM   AdventureWorks2008.Production.Product 
    WHERE  (@item IS NULL 
             OR (@itemList IS NULL 
                 AND ProductNumber = @item) 
                 OR (@item is not null and @itemlist is not null)) 
           AND (@itemList IS NULL 
                 OR ProductNumber 
                 IN (SELECT StringLiteral 
                                   FROM   dbo.fnSplitStringListXML(@ItemList,','))) 
    GO
    -- (504 row(s) affected)
    DECLARE @item varchar(128)='FR-T98Y-60', @itemlist varchar(1024)
    SELECT * 
    FROM   AdventureWorks2008.Production.Product 
    WHERE  (@item IS NULL 
             OR (@itemList IS NULL 
                 AND ProductNumber = @item) 
                 OR (@item is not null and @itemlist is not null)) 
           AND (@itemList IS NULL 
                 OR ProductNumber 
                 IN (SELECT StringLiteral 
                                   FROM   dbo.fnSplitStringListXML(@ItemList,',')))
    GO
    /*
    ProductID	Name	ProductNumber
    885	HL Touring Frame - Yellow, 60	FR-T98Y-60
    */
    DECLARE @item varchar(128), @itemlist varchar(1024)='BK-R89B-58,BK-R64Y-38,BK-R64Y-40'
    SELECT * 
    FROM   AdventureWorks2008.Production.Product 
    WHERE  (@item IS NULL 
             OR (@itemList IS NULL 
                 AND ProductNumber = @item)
                 OR (@item is not null and @itemlist is not null)) 
           AND (@itemList IS NULL 
                 OR ProductNumber 
                 IN (SELECT StringLiteral 
                                   FROM   dbo.fnSplitStringListXML(@ItemList,',')))
    GO
    /*
    ProductID	Name	ProductNumber
    796	Road-250 Black, 58	BK-R89B-58
    797	Road-550-W Yellow, 38	BK-R64Y-38
    798	Road-550-W Yellow, 40	BK-R64Y-40
    */
    
    DECLARE @item varchar(128)='FR-T98Y-60', 
            @itemlist varchar(1024)='BK-R89B-58,BK-R64Y-38,BK-R64Y-40'
    SELECT * 
    FROM   AdventureWorks2008.Production.Product 
    WHERE  (@item IS NULL 
             OR (@itemList IS NULL 
                 AND ProductNumber = @item) 
                 OR (@item is not null and @itemlist is not null)) 
           AND (@itemList IS NULL 
                 OR ProductNumber 
                 IN (SELECT StringLiteral 
                                   FROM   dbo.fnSplitStringListXML(@ItemList,',')))
    GO
    /*
    ProductID	Name	ProductNumber
    796	Road-250 Black, 58	BK-R89B-58
    797	Road-550-W Yellow, 38	BK-R64Y-38
    798	Road-550-W Yellow, 40	BK-R64Y-40
    */
    

    Link for string splitter table-valued function:

    http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

    • Marked as answer by ASP808 Tuesday, March 30, 2010 2:19 AM
    Monday, March 29, 2010 10:15 PM
  • I agree. There is actually a front end hard coded to pass values to the query parameters. I just had no choice while working with a vendor's software without documentation. I think the better approach is to get the vendor to revised the code to use only one parameter.

    However, Your code is awesome.

    I understand the difference to your original code is with the addtion of

    OR (@item is not null and @itemlist is not null)) which take cares of the 3 condition of when both parameters are filled.

    Thanks a bunch.

    Tuesday, March 30, 2010 2:18 AM