Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"Select Where In" using a parameter?


"Select Where In" using a parameter?

Author
Message
Joe90-646727
Joe90-646727
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
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!
Christopher Stobbs
Christopher Stobbs
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1660 Visits: 2232
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
Numbers / Tally Tables

SQL-4-Life
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2462 Visits: 3135
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



My blog: http://jahaines.blogspot.com
antonio.collins
antonio.collins
SSC-Addicted
SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)

Group: General Forum Members
Points: 492 Visits: 921
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, ','))
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2462 Visits: 3135
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(''))





My blog: http://jahaines.blogspot.com
Joe90-646727
Joe90-646727
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
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 ID FROM @x.nodes('//i') x(i)) b



Thanks for your help.
antonio.collins
antonio.collins
SSC-Addicted
SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)

Group: General Forum Members
Points: 492 Visits: 921
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.
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2462 Visits: 3135
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 ID FROM @x.nodes('//i') x(i)) b

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



My blog: http://jahaines.blogspot.com
Madhivanan-208264
Madhivanan-208264
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 476
Also refer http://www.sommarskog.se/arrays-in-sql.html



Madhivanan

Failing to plan is Planning to fail
StarNamer
StarNamer
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1334 Visits: 1992
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 like
DECLARE @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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search