"Select Where In" using a parameter?

  • Hi there,

    this seems like it should be simple, but is a little frustrating!

    I have an sp which takes a list of keys like "12,13,14,15,16" as a varchar param.

    I would like to execute the following query in the sp:

    select * from tableName where id in (@id_list)

    I receive the error "Unable to convert between varchar and int".

    An easy way to reproduce is to run the following code

    declare @id_list varchar(4000)

    set @id_list='313352,313353'

    select * from tableName where id in (@id_list)

    I think dynamic sql might get around it, but I need the sp to be pretty efficient and would prefer not to have to resort to it. Is there a way of doing this without resorting to dynamic SQL?

    Any help would be great!

  • HI,

    It's because your ID column is an Interger and you are comparing it to a string of value, '313352,313353'

    Hope this helps.

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • you could use xml

    DECLARE @x XML

    SET @x = '<i>' + REPLACE( @filter, ',', '</i><i>') + '</i>'

    SELECT *

    FROM MyTable

    WHERE [Status]

    IN (SELECT x.i.value('.', 'VARCHAR(7)')

    FROM @x.nodes('//i') x(i))

    edited to fix xml tags

  • look at the post from GSquared regarding the StringParser in this thread: http://www.sqlservercentral.com/Forums/Topic439094-338-2.aspx

    with it your statement becomes:

    select * from table join dbo.stringparser(@id_list, ',') as list

    on table.id = list.parsed

    or

    select * from table where id in (select parsed from dbo.stringparser(@id_list, ','))

  • I would diffently do the join. You can either use the XML I provided or a udf. I would prefer the xml though. I dont know how the performance will differ but XML get parsed extremely fast in 2005.

    DECLARE @x XML

    SET @x = '<i>' + REPLACE( @IDString, ',', '</i><i>') + '</i>'

    SELECT *

    FROM MyTable a

    INNER JOIN

    (SELECT x.i.value('.', 'VARCHAR(7)')

    FROM @x.nodes('//i') x(i)) b

    ON a.ID = b.ID

    Additionally, I dont know how you create your delmited string but XML can also do this very, very quickly. See below:

    DECLARE @IDString VARCHAR(MAX)

    SET @IDString =

    (SELECT (ID) + ','

    FROM MYTABLE

    --WHERE FILTER CAN GO HERE

    FOR XML PATH(''))

  • Hi guys,

    thanks a million - I checked out both methods and they both work great. I decided to go with the xml method as it means that I don't need to create ufn's or user tables in the db.

    By the way in the xml method I had to assign an alias for the returned column i.e.

    select * from (SELECT x.i.value('.', 'VARCHAR(7)')FROM @x.nodes('//i') x(i)) b

    should read

    select * from (SELECT x.i.value('.', 'VARCHAR(7)') as IDFROM @x.nodes('//i') x(i)) b

    Thanks for your help.

  • change

    SELECT x.i.value('.', 'VARCHAR(7)') as ID

    to

    SELECT x.i.value('.', 'int') as ID

    if your IDs are indeed ints and not text.

  • SELECT x.i.value('.', 'VARCHAR(7)') as ID

    to

    SELECT x.i.value('.', 'int') as ID

    if your IDs are indeed ints and not text.

    Yes, I created this generically without knowing your data. You should change this to accommodate your data.

    select * from (SELECT x.i.value('.', 'VARCHAR(7)') as IDFROM @x.nodes('//i') x(i)) b

    :hehe: I guess I did forget to alias the column. Sorry about that.

  • Also refer http://www.sommarskog.se/arrays-in-sql.html


    Madhivanan

    Failing to plan is Planning to fail

  • Adam Haines (2/19/2008)


    DECLARE @IDString VARCHAR(MAX)

    SET @IDString =

    (SELECT (ID) + ','

    FROM MYTABLE

    --WHERE FILTER CAN GO HERE

    FOR XML PATH(''))

    This leaves a trail comma. I usually do something likeDECLARE @IDString VARCHAR(MAX)

    SET @IDString =

    (SELECT

    CASE row_number() OVER(ORDER BY ID)

    WHEN 1 THEN ''

    ELSE ','

    END + (ID)

    FROM MYTABLE

    --WHERE FILTER CAN GO HERE

    FOR XML PATH(''))

    Derek

  • Adam Haines (2/19/2008)

    --------------------------------------------------------------------------------

    DECLARE @IDString VARCHAR(MAX)SET @IDString =(SELECT (ID) + ','FROM MYTABLE--WHERE FILTER CAN GO HEREFOR XML PATH(''))

    This leaves a trail comma. I usually do something likeDECLARE @IDString VARCHAR(MAX)SET @IDString =(SELECT CASE row_number() OVER(ORDER BY ID) WHEN 1 THEN '' ELSE ',' END + (ID)FROM MYTABLE--WHERE FILTER CAN GO HEREFOR XML PATH(''))

    Yes, it does leave a trailing character. I typically use a method like the one you posted, but for some reason I did not here :hehe:. I mainly wanted to see if the OP was interested in using XML to generate his delimited string. I did not get a response, so I assume the method he is using is adequate for his use.

    Thanks for pointing this out Derek. 🙂

  • Thanks guys - I don't need ot create a delimited string - the list is coming from use input (selected items in a datagrid).

  • Just did some tests.

    The XML version is faster, significantly so, than the Numbers table version, for parsing out a string.

    Has the added advantage of being able to take a multi-character delimiter if needed.

    Of course, it won't work in SQL 2000 (correct me if I'm wrong on that), in which case the Numbers table version is the fastest I've found.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared thanks for running the numbers. I know that the XML is the fastet method I have seen, but I had never gotten around to running the numbers.

  • In the tests I did, the XML method was slightly faster, but required less scans and reads from disk/cache. Speed differences, in many cases, were as few as 1 or 2 milliseconds. But the reduced reads and scans means less I/O bottleneck. May not matter on some systems, but worth it in many cases.

    (I ran the same tests on a While loop version, and both XML and Numbers versions were consistently at least twice as fast, many times three or more times faster, and in at least one case it was impossible to judge because XML and Numbers ran in less than a millisecond but the While loop took 37 milliseconds. The While loop, on the other hand, also requires less I/O than the Numbers table. If CPU resources are less of a bottleneck on a server than I/O, and XML isn't an option, the While loop might be viable.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 20 total)

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