IN Clause Question

  • The top part of this works. I'm trying to pass to an "IN" list on the bottom. What is the cleanest way to do this? I'd like the set to be like this if possible 'dog', 'mouse', 'cat'

    IF OBJECT_ID('tempdb..#Rookie') > 0 DROP TABLE #Rookie

    CREATE TABLE #Rookie

    (Id varchar(10), CriteriaA int, CriteriaB varchar(20))

    INSERT INTO #Rookie

    (Id, CriteriaA, CriteriaB)

    SELECT 3111, 1, 'dog' UNION ALL

    SELECT 4111, 1, 'mouse' UNION ALL

    SELECT 5111, 1, 'cat' UNION ALL

    SELECT 6111, 2, 'fly' UNION ALL

    SELECT 7111, 3, 'bug'

    SELECT * FROM #Rookie

    WHERE CriteriaA IN (1, 2)

    AND CriteriaB IN ('dog', 'mouse', 'cat', 'fly')

    --this does not work

    /*

    DECLARE @CriteriaA int, @CriteriaB varchar(MAX)

    SET @CriteriaA = 1, 2

    SET @CriteriaB = ('dog', 'mouse', 'cat')

    SELECT * FROM #Rookie

    WHERE CriteriaA IN @CriteriaA

    AND CriteriaB IN @CriteriaB

    */

  • You have wrongly declared @criteria as int. It should be varchar. First you have to form a query. Then execute it..

    Pls check the below code.

    DECLARE @CriteriaA VARCHAR(10), @CriteriaB varchar(MAX)

    SET @CriteriaA = '1, 2'

    SET @CriteriaB = '(''dog'', ''mouse'', ''cat'')'

    declare @sql varchar(max)

    Set @sql = 'SELECT * FROM #Rookie

    WHERE CriteriaA IN (' + @CriteriaA + ') AND CriteriaB IN '+ @CriteriaB

    Print @sql

    exec(@sql)

    Thanks

  • Thanks, your solution makes sense.

  • Personally I wouldn't use Dynamic SQL for this.

    My suggestion is to pass the two lists of values (@CriteriaA and @CriteriaB) as delimited strings and use a string splitter like Jeff Moden's (community) DelimitedSplit8K function to split them and pass that result into the IN.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (8/7/2012)


    Personally I wouldn't use Dynamic SQL for this.

    My suggestion is to pass the two lists of values (@CriteriaA and @CriteriaB) as delimited strings and use a string splitter like Jeff Moden's (community) DelimitedSplit8K function to split them and pass that result into the IN.

    +1000000

    There are very few absolutes in sql server but directly executing a parameter is one of them. If you use the dynamic sql approach you took your procedure and made it instantly vulnerable to sql injection.

    Since you posted in the 2008 forum I would suggest that and even better approach than parsing this is to use a table valued parameter instead.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Must agree with Sean and Dwain.

    Either pass in separated values and split the strings to a temporary table/table variable and join to that, or if you feel comfortable pass in a tvp and use it directly in the join.

    IN righfully sucks with parameters.... IN also with large quantities of values tends to suck on performance.

    IN is nothing more than shorthand for multiple OR = 's tests.... enough of those will choke a query.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply