Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

"Select Where In" using a parameter? Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2008 7:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 24, 2008 3:47 AM
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!
Post #457395
Posted Tuesday, February 19, 2008 8:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #457415
Posted Tuesday, February 19, 2008 8:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:34 AM
Points: 2,278, Visits: 3,046
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
Post #457416
Posted Tuesday, February 19, 2008 9:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:58 PM
Points: 438, Visits: 902
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, ','))
Post #457460
Posted Tuesday, February 19, 2008 10:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:34 AM
Points: 2,278, Visits: 3,046
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
Post #457527
Posted Wednesday, February 20, 2008 6:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 24, 2008 3:47 AM
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.
Post #457957
Posted Wednesday, February 20, 2008 7:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:58 PM
Points: 438, Visits: 902
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.
Post #457958
Posted Wednesday, February 20, 2008 7:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:34 AM
Points: 2,278, Visits: 3,046
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

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




My blog: http://jahaines.blogspot.com
Post #457973
Posted Thursday, February 21, 2008 7:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:07 AM
Points: 329, Visits: 468
Also refer http://www.sommarskog.se/arrays-in-sql.html



Madhivanan

Failing to plan is Planning to fail
Post #458534
Posted Friday, February 22, 2008 6:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #459057
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse