I'm trying to determine the max length allowable for a sql query. The query form is
The above query is partly constructed by a UDF. The UDF supplies the WHERE clause from list of keys in a cursor. The list can grow, and when it does, I get the "statement too long" error.
BTW, the UDF already handles the max 24 keys limit for "IN (...)" list. So, as you can imagine there can be several "IN (...)" lists in the WHERE clause, each separated by "OR".
I'm thinking of batching the "IN (...)" into groups of 5 only, but, I'm not sure if this can handle larger lists. So, I'm wondering what's the ideal way of working through this.
What I recently tested includes 7 lists and it failed. I removed 1 and it ran fine.
In case you're wondering why I need to do this, its because I wan't to minize backend trips.
I'll appreciate any help I can get. For now, I'll try doing the 5 per group until I get a better approach.
Thanks!
Code:
SELECT * FROM table1 WHERE cfield1 IN (...)
The above query is partly constructed by a UDF. The UDF supplies the WHERE clause from list of keys in a cursor. The list can grow, and when it does, I get the "statement too long" error.
BTW, the UDF already handles the max 24 keys limit for "IN (...)" list. So, as you can imagine there can be several "IN (...)" lists in the WHERE clause, each separated by "OR".
I'm thinking of batching the "IN (...)" into groups of 5 only, but, I'm not sure if this can handle larger lists. So, I'm wondering what's the ideal way of working through this.
What I recently tested includes 7 lists and it failed. I removed 1 and it ran fine.
In case you're wondering why I need to do this, its because I wan't to minize backend trips.
I'll appreciate any help I can get. For now, I'll try doing the 5 per group until I get a better approach.
Thanks!