SQL Server list too long

Question

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!

Put your list in a temp table and use `id in (select id from #temp)` — Dale K, Oct 07 '20 at 20:59
Does this answer your question? [Limit on the WHERE col IN (...) condition](https://stackoverflow.com/questions/1069415/limit-on-the-where-col-in-condition) — GSerg, Oct 07 '20 at 21:05
Using localized date literals just begs for trouble. What does `4/7/2020` mean? July 4th or April 7th? Use the unambiguous `YYYYMMDD` format or pass the date as a date-typed parameter — Panagiotis Kanavos, Oct 07 '20 at 21:09
`Does anyone know a better way to do this` don't use a list. If the data comes from the database, put the query that generates those IDs in the `IN` clause. If they come from the client, you can insert them into a temporary table and join, use a table-valued parameter, or use a [table value constructor](https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15). All options have their tradeoffs — Panagiotis Kanavos, Oct 07 '20 at 21:11

score 2 · Answer 1 · answered Oct 07 '20 at 21:13

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;

score 1 · Answer 2 · answered Oct 08 '20 at 06:34

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;

SQL Server list too long

2 Answers2