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.

Answer 1

Use join instead, this should work

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