SQL - where Id in (list of Ids)

Question

4.50/5 (2 votes)

See more:

SQL

--SQL EXECUTION QUERY --
declare @Ids varchar(100) set @Ids='7,13,120'
select * from table where Id in (@Ids)
--SQL EXECUTION QUERY --

This results me "Conversion failed when converting the varchar value '7,13,120' to data type int."

I want to implement "WHERE IN" query on integer type but my values will be stored in a local variable which can be of any type int or varchar.

Posted 9-Oct-12 5:23am

Pinank_CD

Updated 9-Oct-12 5:32am

Kenneth Haugland

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dylan Morley · Answer 1 · 2012-10-09T05:49:00

Have a look at this question on StackOverflow, which is similar to what you want to do

http://stackoverflow.com/questions/1707326/define-variable-to-use-with-in-operator-t-sql[^]

Personally, I like the answer by LukeH[^], e.g.

SQL

DECLARE @MyList TABLE (Value INT)
INSERT INTO @MyList VALUES (1)
INSERT INTO @MyList VALUES (2)
INSERT INTO @MyList VALUES (3)
INSERT INTO @MyList VALUES (4)

SELECT *
FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

Have a read through the different options available to you

Santhosh Kumar Jayaraman · Answer 2 · 2012-10-09T05:51:00

Solution 2

try this

SQL

declare @Ids varchar(100)
set @Ids='100,200,300'
declare @query as nvarchar(max)
set @query='select * from table where Id in (' +@Ids +')'
exec sp_executesql @query

Posted 9-Oct-12 5:51am

Santhosh Kumar Jayaraman

Comments

Pinank_CD 9-Oct-12 12:01pm

it would be beneficial if something could work with easy casting or convert or replace. because in my SP, i have to use that variable at different levels but anyways all of the solutions were worth using.

a million thanks to all of the repliers, specially Mr.Dylan Morley and Mr.Santhosh Kumar.

damodara naidu betha · Answer 3 · 2012-10-09T20:27:00

Solution 3

Hi Pinak,

your query

SQL

select * from table where Id in ('7,3,120')

returns error, because it is comparing integer column with varchar value ie. '7,3,120'.
Sql server engine tries to parse the varchar value into integer. Because of ',', it gives error.

Following query will return the result set ...

SQL

select * from table where Id in ('7','3','120')

But still it is static.To make it dynamic, you need to split the varchar value.
Following query helps to get the result set with out getting any error.

SQL

DECLARE @ids VARCHAR(100) = '7,3,120';
DECLARE @Xparam XML;
--Convert @ids to XML 
SELECT @Xparam = CAST('<i>' + REPLACE(@ids,',','</i><i>') + '</i>' AS XML)

--Query to compare the id against the ids result set by splitting the XML nodes
-- as a result set
SELECT * FROM Yourtable WHERE Id IN (SELECT x.i.value('.','INT') FROM @Xparam.nodes('//i') x(i))

Thank you..

Posted 9-Oct-12 20:27pm

damodara naidu betha

Updated 10-Oct-12 0:13am

v6

Comments

Pinank_CD 10-Oct-12 4:01am

Hi Mr.Betha,

Thanks for your reply.

your code isn't seems working. even i tried with the below simple query but nothing resulted.

-------------EXECUTION START--------------
DECLARE @param VARCHAR(MAX) = '3,24,2,45';
DECLARE @Xparam XML;

SELECT @Xparam = CAST('' + REPLACE(@param,',','') + '' AS XML)
SELECT x.i.value('.','INT') FROM @Xparam.nodes('//i') x(i)
-------------EXECUTION ENDS--------------

Also can you please provide more comments on subquery (SELECT x.i.value.....)

damodara naidu betha 10-Oct-12 6:22am

I had problem with the editor. I have modified it, now you can test the query. Here I am explaining the sub query. First I am replacing the ',' in the @param value with . So the entire varchar becomes 324245. After that I am casting it into XML type and storing in @Xparam of type XML. If I run the
SELECT x.i.value('.','INT') FROM @Xparam.nodes('//i') x(i) .. it returns a result set like ..
3
24
2
45
This is called XQuery( querying XML types).

Pinank_CD 10-Oct-12 6:51am

your explanation was really helpful. i am sorry but i dint found the modified query in your comments.

can you modify the below query and resend.

DECLARE @param VARCHAR(MAX) = '3,24,2,45';
DECLARE @Xparam XML;

SELECT @Xparam = CAST('' + REPLACE(@param,',','') + '' AS XML)
SELECT x.i.value('.','INT') FROM @Xparam.nodes('//i') x(i)

damodara naidu betha 10-Oct-12 8:13am

I have modified the answer, and here is your query....
DECLARE @param VARCHAR(MAX) = '3,24,2,45';
DECLARE @Xparam XML;

SELECT @Xparam = CAST('' + REPLACE(@param,',','') + '' AS XML)
SELECT x.i.value('.','INT') FROM @Xparam.nodes('//i') x(i)

Pinank_CD 10-Oct-12 11:05am

It worked. thanks a lot Mr.Betha