As facts show below:

1) Maximum size of an SQL statement is 64K
2) The maximum length of a DECLARE statement is 64 kilobytes.
3) Combined size of procedure code and its global variables are limited
to 64K per procedure

I have made a SP which takes a input parameter as the data type of "LIST". when I call this SP with the input parameter LIST containing over 3000 string (the string length is 10), I am not able to run this SP.

My guess is the problem of 64K limitation. Am I right?

Is there any possibilities and solutions to overcome the constrains? Appreciate if there is a way to do it!

Hi,


An error number, when you run the procedure, would probably help to focus troubleshooting :-)

Eric

After running the procedure, I have got the error code:

460: Statement length exceeds maximum.


Example:

A) - To run the example

dbaccess utvdb1 64kInvestigation.sql

Database selected.

460: Statement length exceeds maximum.
Error in line 1
Near character position 1

Database closed.


B) - To find the meaning of error

finderr 460
-460 Statement length exceeds maximum.

The statement text in this PREPARE, DECLARE, or EXECUTE IMMEDIATE
statement is longer than the database server can handle. The actual
limit differs with different implementations, but it is always
generous, in most cases up to 32,000 characters. Review the program
logic to ensure that an error has not caused it to present a string
that is longer than intended (for example, by overlaying the null
string terminator byte in memory). If the text has the intended length,
revise the program to present fewer statements at a time.


How to overcome the limitation?

Hi,

why don't you try to insert your parameters list into some (temp ) table
before calling the proc, then the proc reads those parameters from the (temp) table.

Passing 3000 arguments to a function or stored procedure is a bit extreme for me. Who can read and maintain this code ?

Cheers
Eric

Thanks for reply and help.

I can read and maintain this code. But I can not control the size of parameters list. The client side is written in C# of .Net, it calls this SP and return the values.

According to your suggestion, Do you mean that it is to take all the list parameters and save them into a temp table, and fetch 3000 arguments to the function for each call??

Yes,

this is the idea!
1) insert one row per argument intot the temp table ( prepare your insert statement for faster response time )

2) in the stored proc, if you arguments are by chance part of the cursor where clause, you can use something like:

Or something similar

Don't forget to suppress the contents of the temp table after...

This will be a bit slower than passing the arguments, but here you have not much choice...


Hope this helps

Thanks for your suggestion.

Here it is a problem by following your advise,

As the client would like to take a list of parameters with the length size 1000000 (the size of parameters is varied),

I wrote a SP for saving 1000000 parameters (LIST type) to a temp table.
it gives the same error: 460: Statement length exceeds maximum when I run "dbaccess utvdb1 64kInvestigationSaveTemp.sql" .

My question is:

How to provide a SP call to the client, which client can take no limit length of parameters LIST? Should I provide a SP for client to take 3000 parameters for saving them into a temp table??

Appreciate your help!

Your custome's request goes in favour of my suggestion.

Before the SP, insert each value of the parameter into one row of the temp table, and consider having an additional INTEGER column in this table for sorting purpose if necessary.

In the stored procedure, read the parameters in a foreach loop, or execute an inner join with the other tables, as explained above.