Using SQL IN Clause

Question

I am having a query like :

declare @aID varchar(max)='1'
declare @pID varchar(max)=' '
SELECT * from Table1 WHERE 
(PId = @PID or @pID='') AND (AID = @aID or @aID = '')

This query works fine. But now, I want to use 'IN' clause.

declare @aID varchar(max)='1,2'
SELECT * from Table1 WHERE 
(PId = @PID or @pID='') AND (AID IN (@aID) or @aID = '')

But this last query gives me an error.

Error : 'Conversion failed when converting the varchar value '1,2' to data type int'

I do not want to change the design of my Table1

How can I make this possible ?

Declaring your parameter AS XML offers you the chance to pass what ever structured data you want into a SP or and UDF... It's quite easy to deal with (simple) XML, SQL-Server does a great job here... — Gottfried Lesigang
– Gottfried Lesigang, Commented Jul 10, 2015 at 21:45

Marc B · Accepted Answer · 2015-07-10 19:11:10Z

1

@vars are inserted as a single "chunk". it's not the DB's job to decide that it's actually MULTIPLE separate comma-separated values.

If you want 1,2 to be treated as two values, you''ll have to use dynamic sql, e.g.

query = "SELECT ... IN (" + @var + ")";
exec query;

answered Jul 10, 2015 at 19:11

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

hello temp11 Over a year ago

I did that, it works only If i specify a value. If no value is passed then I get an error. For example query = "SELECT ... IN (" + @var + ") or (" + @var + ") = '''' "; exec query; will give me an error when I pass empty spring

Marc B Over a year ago

then put in some conditinoal log to add the in clause if you pass in something, otherwise skip it entirely.

Conrad Frix Over a year ago

MarcB the OP wants @AID to be eventually be a paramter from a strored proc. I don't think I need to say why this changes things

Community · Accepted Answer · 2017-05-23 12:06:04Z

0

Rather than trying to use a comma dilimited value for your in you might want to use a table variable

declare @ids table ( aid int ) ;
INSERT INTO @ids (aid) Values (1)(2);

declare @idCount  int;
SELECT @idCount = COUNT(aID) FROM @ids;  

SELECT * from Table1 
WHERE 
(PId = @PID or @pID='') 
 AND (
         AID IN (SELECT aid from @ids) 
       or @idCount =0)

If you really wanted to keep the comma delimited values you can also use a split function

SELECT * from Table1 
    WHERE 
    (PId = @PID or @pID='') 
     AND (
             AID IN (SELECT item from dbo.SplitString(@aid, ',') ) 
           or @aid = "")

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Jul 10, 2015 at 19:18

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

7 Comments

hello temp11 Over a year ago

I am having this as a Store Proc. How can I insert values 1,2 ?

Conrad Frix Over a year ago

Are you saying that @aID is a parameter?

hello temp11 Over a year ago

For the other part, I get Error as Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

hello temp11 Over a year ago

Yes, @aID is a Parameter.

Conrad Frix Over a year ago

You should have said. In that case see Parameterize an SQL IN clause I pefer this answer which uses table-values parmeters

|

Collectives™ on Stack Overflow

Using SQL IN Clause

2 Answers 2

3 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related