Query WHERE IN clause not working

Question

In a SQL query we are using WHERE IN clause to filter the data. When I am passing 35000 fields in WHERE IN clause, The ExecuteNonQuery throws

Object reference not set to an instance exception

I have used try catch in where the ExecuteNonQuery excuted, but the exception not catched current method, it catched in parent method (button click)

If I reduced the count from 35000 to 25000 the SQL query works fine. Please help.

SELECT * FROM COUNTRY WHERE CountryID in ('1','2',......'35000')

I have tried to use Temp Table in SQL also, Same error happened.

IF OBJECT_ID('tempdb..#temp)IS NOT NULL DROP #TEMP 
CREATE TABLE #TEMP
( CountryID int NULL)
INSERT INTO #TEMP VALUES ('1')
.
.
.
INSERT INTO #TEMP VALUES('10')

SELECT * FROM COUNTRY WHERE CountryID IN(SELECT CountryID from #temp)

The object null reference error is not the problem, How can i overcome the Where In clause issue in sql query. What would the possible resolution to avoid the Where in clause in sql query.

Please help. Thanks.

Possible duplicate of [What does "Object reference not set to an instance of an object" mean?](http://stackoverflow.com/questions/779091/what-does-object-reference-not-set-to-an-instance-of-an-object-mean) — Matt Wilko, Apr 07 '16 at 07:35
If CountryID are integers why do you put them inside your IN as strings? — Steve, Apr 07 '16 at 07:42
OT, there are only about 200 countries in the whole world... — jarlh, Apr 07 '16 at 07:44
@Steve Can we avoid the exception when we use the CountryID as integer in WHERE IN? — Cegone, Apr 07 '16 at 07:49
35000 items in a IN are a lot of data. Anyway, if the field is integer then passing string will be only a possible source of troubles. Just try it — Steve, Apr 07 '16 at 07:51
@jarlh: i put countryID is just for example.. :) but in project different key names we are using. — Cegone, Apr 07 '16 at 07:51
@jarlh from his temp table code I *assume* it´s SQL Server. Anyway Cegone should clarify this and also tell us which version of dbms he´s using. — Alex B., Apr 07 '16 at 07:58
@Steve : Thanks for you tip, It's working when i pass the ConutryID's as integer without single quote. I could fetch more than 35000 items. — Cegone, Apr 08 '16 at 08:55

score 3 · Accepted Answer · answered Apr 07 '16 at 07:51

3

Use join instead, this should work

SELECT * FROM COUNTRY c INNER JOIN #temp t on c.CountryId=t.CountryId

answered Apr 07 '16 at 07:51

Pawan Nogariya

8,330
12
52
105

It it worked for you, please mark the answer correct :) – Pawan Nogariya Apr 25 '16 at 09:15

crimson589 · Answer 2 · 2016-04-07T07:38:41.577

0

Why not just use between?

SELECT * FROM COUNTRY WHERE CountryID between 1 and 10

edited Apr 07 '16 at 07:38

answered Apr 07 '16 at 07:37

crimson589

1,238
1
20
36

2

Perhaps there are gaps? – jarlh Apr 07 '16 at 07:38
@jarlh I don't know how he gets what ID to select but you can use `OR`, `SELECT * FROM COUNTRY WHERE CountryID between 1 and 4 or CountryID between 8 and 10` – crimson589 Apr 07 '16 at 07:40
@Cegone Ah.. ok, sorry. Your first example made me think it's just 1-35000 – crimson589 Apr 07 '16 at 07:45

Query WHERE IN clause not working

2 Answers2