February 19, 2008 at 7:48 am
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!
February 19, 2008 at 8:05 am
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]
February 19, 2008 at 8:06 am
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
February 19, 2008 at 9:09 am
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, ','))
February 19, 2008 at 10:42 am
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(''))
February 20, 2008 at 6:58 am
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.
February 20, 2008 at 7:03 am
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.
February 20, 2008 at 7:24 am
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.
February 21, 2008 at 7:03 am
Also refer http://www.sommarskog.se/arrays-in-sql.html
Failing to plan is Planning to fail
February 22, 2008 at 6:17 am
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
February 22, 2008 at 7:22 am
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. 🙂
February 22, 2008 at 7:32 am
Thanks guys - I don't need ot create a delimited string - the list is coming from use input (selected items in a datagrid).
March 3, 2008 at 11:17 am
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
March 3, 2008 at 12:02 pm
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.
March 5, 2008 at 11:55 am
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