CODEWITHSUNDEEP
sqlsql-server
Catalina Radu
Catalina Radu

Reputation: 17

SQL Server list too long

I am trying to run this SQL statement:

select * 
from table
where 1 = 1
  and date >= '1/1/2020'
  and id = any (**'list of 1300 items'**)
order by date asc;

The issue is that my list in the second and statement is actually over 1000 expressions, so it does not let me run this statement. Does anyone know a better way to do this? Basically, I have and ID that I want to locate in our database and the easiest way I know to do this is with a list but clearly mine is too long. I'm new to this so any insight would be greatly appreciated!

Upvotes: 1

Views: 293

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

You can think of string_split, if your SQL Server version is 2016 or later. Thanks to @Cetin Bazos for the basic script.

DECLARE @ids NVARCHAR(MAX) = '1,3,34,434,43' -- your 1300+ ids

select * 
from yourtable
where [date] >= '20200101'
  and id IN (SELECT [value] FROM STRING_SPLIT(@ids,','))
order by [date] asc;

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

There are multiple ways to do that. For example, if your SQL server version is not old, you could use openJSON(). ie:

DECLARE @ids VARCHAR(MAX) = '[1,3,34,434,43]' -- your 1300+ ids

select * 
from yourtable
where [date] >= '20200101'
  and id IN (SELECT [value] FROM OPENJSON(@ids))
order by [date] asc;

Upvotes: 2

Related Questions