Results 1 to 6 of 6

Thread: SQL Syntax Help

  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Unanswered: SQL Syntax Help

    Is it possible to pass a comma delimited list of integers as a parameter into an IN function like this?

    SELECT *
    FROM MyTable
    WHERE MyID IN (@MyVar)

    'MyID' is an INT datatype and I'd like '@MyVar' to equal 1,2,3.

    Many thanks in advance.

    TML

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I generally feed my CSVs into a table with something like this....

    Simple SQL CSV to Table Code Bank

    and then I join to the table.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    try like this
    declare @MyVar varchar(32)
    set @MyVar = '1,5,7'

    SELECT *
    FROM mytable
    WHERE '%,'+ @MyVar+',%' like '%,'+ convert(varchar(32),myid)+',%'

  4. #4
    Join Date
    Jan 2009
    Posts
    2
    That's perfect!

    Thanks for your help.

    TML

  5. #5
    Join Date
    Dec 2008
    Posts
    135
    welcome yaar

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Be aware that the LIKE solution doesn't scale well. It guarantees a table scan, so on large tables it will be slow.

    The solution that Thrasymachus proposed works against a column value instead of an expression, so it can use indicies. It will scale well against the largest tables you can create using SQL Server.

    -PatP

Posting Permissions

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