Where In Clause problem

Question

Where In Clause problem

mohsinjk

Newbie

Points: 2
More actions
August 2, 2010 at 2:36 am

#224951

Hello,
I am trying to send multiple values in one query using <Where clause "In(@ID)">
@ID varchar(max)
Select * from tblProduct
WHERE tbl_PRIM_Sales.Sale_ID in (@ID)
my logic is
@ID could be
@ID=47 this is ok
but when I pass value in a parameter something like this.. @ID='47,58,98'
It gave m conversion error
'Conversion failed when converting the varchar value '47,58' to data type int.'
Please give me some hints

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

You cant do that ways... You will have to insert the matching values into a temp table and then join them both.. I have another code as well to accomplish this, but thats a semi-cooked one.. i will optimize that and post it as soon as it is ready..

mohsinjk Newbie Points: 2 More actions · Answer 2

mohsinjk

Newbie

Points: 2

August 2, 2010 at 2:48 am

#1200877

Thanks...

I will wait for your solution...

Adiga One Orange Chip Points: 27284 More actions · Answer 3

@ID varchar(max)
Select * from tblProduct
WHERE tbl_PRIM_Sales.Sale_ID in (@ID)

If you are planning to look for fixed number of values in the IN statement, you may declare multiple variable like @ID1,@ID2, @ID3 etc.

and your query would look like

@ID1 varchar(max)

@ID2 varchar(max)

Select * from tblProduct

WHERE tbl_PRIM_Sales.Sale_ID in (@ID1, @ID2)

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Amit kulkarni-491236 Default port Points: 1492 More actions · Answer 4

You can use dynamic sql query

declare @ID varchar(max)

declare @sql nvarchar(4000)

set @sql = 'Select * from tblProduct

WHERE tbl_PRIM_Sales.Sale_ID in (' +@ID + ')'

sp_executesql @sql

Or you can insert all IDs in temp table or table variable or Common table expression and then query the main table by selecting the IDs from temp table/ table variable/Common table expression.

Thanks,

Amit Kulkarni