### Author:  [@fvancrae](https://talk.remobjects.com/u/fvancrae)
#### Post date: [December 10, 2024, 4:23pm UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/1 "2024-12-10T16:23:48Z")
I use a huge amount of parameters for certain (maintenance) queries
However I run into a limitation of RO/DA
Is there a way to bypass this?
It’s used as an ‘IN’ SQL where clause.
var listWhereExpression = new List\();
foreach (int pk in fetchPrimkeyList)
listWhereExpression.Add(new RemObjects.DataAbstract.Expressions.ConstantExpression(pk, RemObjects.DataAbstract.Schema.DataType.Integer));
var listExpression = new RemObjects.DataAbstract.Expressions.ListExpression(listWhereExpression.ToArray());
var condition = new RemObjects.DataAbstract.Expressions.BinaryExpression(
new RemObjects.DataAbstract.Expressions.FieldExpression(KeyFieldName), listExpression, RemObjects.DataAbstract.Expressions.BinaryOperator.In);
whereClauseXml = condition.ToXmlNode();
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [December 10, 2024, 4:54pm UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/2 "2024-12-10T16:54:14Z")
Hi,
this is standard limitation of MSSQL.
from [Maximum capacity specifications for SQL Server - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16)
| SQL Server Database Engine object | Maximum values for SQL Server (64-bit) |
| --- | --- |
| … | |
| Parameters per stored procedure | 2,100 |
| Parameters per user-defined function | 2,100 |
---
### Author:  [@fvancrae](https://talk.remobjects.com/u/fvancrae)
#### Post date: [December 10, 2024, 5:24pm UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/3 "2024-12-10T17:24:14Z")
Hello Evgeny,
1. It does not look like a stored proc is used to do the fetch
This is the SQL
SELECT xxx
FROM yyy
WHERE ([prg\_primkey] IN (@DAPARAM\_1, @DAPARAM\_2, …
2. I don’t want to use a stored procedure
Any way of telling DA to not use a stored proc?
3. I have an alternate solution for my custom where clauses
I use an identifier resolver
EventHandler resolveIdentifier = (IChannelSender, e) =\>…
that’s hooked to DataAbstractService.UnknownSqlMacroIdentifier
which replaces my own {ExtraWhereClause} variable.
This works without limitation but is inconvenient because:
- The DA schema GUI application does not allow “Preview Data” or other execution when the identifier is there
- Easy to forget the identifier in the SQL query
Is there a way to use a custom where clause, e.g. by changing the SQL in the schema at runtime ({where} becomes {where} and {extrawhereclause}
Or failing that is there a way to add a custom identifier that the DA schema modeler ignores?
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [December 11, 2024, 10:18am UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/4 "2024-12-11T10:18:03Z")
Hi,
> [@fvancrae](#):
>
> It does not look like a stored proc is used to do the fetch
> This is the SQL
> SELECT xxx
> FROM yyy
> WHERE ([prg\_primkey] IN (@DAPARAM\_1, @DAPARAM\_2, …
Can you launch MSSQL profiler and see what actual SQL is called and why \> 2100 params are used, pls?
> [@fvancrae](#):
>
> Is there a way to use a custom where clause, e.g. by changing the SQL in the schema at runtime ({where} becomes {where} and {extrawhereclause}
Why you can’t use [MacroExpression](https://docs.dataabstract.com/Technologies/DynamicWhere/MacroExpression/) for passing custom macro?
---
### Author:  [@fvancrae](https://talk.remobjects.com/u/fvancrae)
#### Post date: [December 13, 2024, 9:11am UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/5 "2024-12-13T09:11:17Z")
I didn’t know about macro expressions yet
I read [MacroExpression](https://docs.dataabstract.com/Technologies/DynamicWhere/MacroExpression/)
but the example there does not apply to my situation (furthermore I fail to see how the macro expression leads to SUBSTRING(…)
Do you have an example macro expression for
WHERE fieldX IN (1,5,7)
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [December 13, 2024, 12:12pm UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/6 "2024-12-13T12:12:22Z")
Hi,
> [@fvancrae](#):
>
> fieldX IN (1,5,7)
better to use [ListExpression](https://docs.dataabstract.com/Technologies/DynamicWhere/ListExpression/) :
```auto
WhereExpression where =
new BinaryExpression(
new FieldExpression("","fieldX"),
new ListExpression(
new WhereExpression[]{
new ConstantExpression(1),
new ConstantExpression(5),
new ConstantExpression(7)
}),
BinaryOperator.In);
```
how many items in `IN` list?
---
### Author:  [@fvancrae](https://talk.remobjects.com/u/fvancrae)
#### Post date: [December 13, 2024, 1:08pm UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/7 "2024-12-13T13:08:34Z")
I believe this is the method I was already using but that fails due to the argument limit (= 2100 in my case)
```auto
// FVC 12/2024: This solution does not require the {ExtraWhereClause} in the SQL but is limited to ~2100 parameters - see https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/3
var listWhereExpression = new List();
foreach (int pk in fetchPrimkeyList)
listWhereExpression.Add(new RemObjects.DataAbstract.Expressions.ConstantExpression(pk, RemObjects.DataAbstract.Schema.DataType.Integer));
var listExpression = new RemObjects.DataAbstract.Expressions.ListExpression(listWhereExpression.ToArray());
var condition = new RemObjects.DataAbstract.Expressions.BinaryExpression(
new RemObjects.DataAbstract.Expressions.FieldExpression(KeyFieldName), listExpression, RemObjects.DataAbstract.Expressions.BinaryOperator.In);
whereClauseXml = condition.ToXmlNode();
```
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [December 13, 2024, 1:29pm UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/8 "2024-12-13T13:29:44Z")
Hi,
> [@EvgenyK](#):
>
> how many items in `IN` list?
\> 2100?
---
### Author:  [@fvancrae](https://talk.remobjects.com/u/fvancrae)
#### Post date: [December 13, 2024, 4:36pm UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/9 "2024-12-13T16:36:02Z")
Yes, 7000
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [December 13, 2024, 4:59pm UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/10 "2024-12-13T16:59:20Z")
Is it possible to reduce this list?
you can use combination of [BetweenExpression](https://docs.dataabstract.com/Technologies/DynamicWhere/BetweenExpression/) and [ListExpression](https://docs.dataabstract.com/Technologies/DynamicWhere/ListExpression/) like
- 20, 22…29, 31…50
```auto
(field BETWEEN 20 AND 50) and not (field IN [21,30])
```
-20…50,67,79
```auto
(field BETWEEN 20 AND 50) or (field IN [67,79])
```
---
### Author:  [@fvancrae](https://talk.remobjects.com/u/fvancrae)
#### Post date: [December 14, 2024, 1:10pm UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/11 "2024-12-14T13:10:12Z")
Given the data values that would not guarantee that the number of parameters is always less then 2100
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [December 16, 2024, 11:58am UTC](https://talk.remobjects.com/t/bypass-maximum-number-of-parameters/31172/12 "2024-12-16T11:58:15Z")
Hi,
try to use workaround from this testcase:
[testcase.zip](https://talk.remobjects.com/uploads/short-url/fPTEw3FSPFbVta2O4VQqPyrFhFo.zip) (17.0 KB)
it passes `WhereExpression` from client-side, transforms it to plain SQL and replaces custom macro with this plain sql.
this is safe for `field IN (array of integers)` expression but can be **dangerous** with string values.