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... — Shnugo, Jul 10 '15 at 21:45

score 1 · Answer 1 · answered Jul 10 '15 at 19:11

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 '15 at 19:11

Marc B

356,200
43
426
500

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 – hello temp11 Jul 10 '15 at 19:31
then put in some conditinoal log to add the `in` clause if you pass in something, otherwise skip it entirely. – Marc B Jul 10 '15 at 19:32
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 – Conrad Frix Jul 10 '15 at 19:35

score 0 · Answer 2 · edited May 23 '17 at 12:06

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 '17 at 12:06

Community

1
1

answered Jul 10 '15 at 19:18

Conrad Frix

51,984
12
96
155

I am having this as a Store Proc. How can I insert values 1,2 ? – hello temp11 Jul 10 '15 at 19:26
Are you saying that @aID is a parameter? – Conrad Frix Jul 10 '15 at 19:27
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 Jul 10 '15 at 19:28
Yes, @aID is a Parameter. – hello temp11 Jul 10 '15 at 19:29
You should have said. In that case see [Parameterize an SQL IN clause](http://stackoverflow.com/q/337704/119477) I pefer [this answer](http://stackoverflow.com/a/337864/119477) which uses table-values parmeters – Conrad Frix Jul 10 '15 at 19:32
Also the error you're getting with split is because you wrote `IN (Select * from..)` you need to write `IN (SELECT item ... ` – Conrad Frix Jul 10 '15 at 19:34
WHen I use your splitstring query and keep OR between Pid and Aid, I always get all the result despite of keeping values. – hello temp11 Jul 10 '15 at 19:51

Using SQL IN Clause

2 Answers2