0

I need to write a T-SQL query with something like:

WHERE 
    tbl.field IN (%a lot of variants%)

But I can't because of limitation of 2100 items for parameters count in SQL Server.

How can I find the way around this problem?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Artyom Zajtsev
  • 13
  • 4
  • Use a table type parameter/variable instead. `IN`, however, can support way more than 2,100 inline values; it's not till you get many thousands of values that you start to get performance issues either. – Thom A Mar 16 '20 at 11:49
  • Your question doesn't stipulate, but I suspect you are experiencing limitation when using LINQ. Can you confirm? https://stackoverflow.com/questions/656167/hitting-the-2100-parameter-limit-sql-server-when-using-contains – Matt Evans Mar 16 '20 at 11:57
  • This [DB<>Fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=958e9cbd04ae024222d9057af99c1a6b), for example, uses an `IN` with **10,000** inline values and doesn't error. – Thom A Mar 16 '20 at 11:58
  • Thank you all for answers! But apparently my proplem was caused by HQL "IN" clause. So i rewrote it and problem was solved. – Artyom Zajtsev Mar 19 '20 at 07:53

2 Answers2

2

One easy way is to use OPENJSON, and pass the list not as individual parameters, but as a single NVARCHAR(MAX) parameter that's parsed on the server. EG

select * 
from large 
where id in (select cast(value as int) from openjson(@values))

Where @values is a parameter of type NVARCHAR(MAX) containint a JSON Array of scalars. Like

var values = "[1,2,3,4,5,6,7,8]";

And as a bonus it performs much better too.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
0

You need to you a subquery! 

SELECT VAR1, VAR2    
FROM TABLE
WHERE 
  (SELECT.. FROM.. ETC)
Thom A
  • 88,727
  • 11
  • 45
  • 75
AndreasCtrl
  • 1