Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Unanswered: 64K limitation for the stored procedure (any solutions!!!?)

    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!

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,


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

    Eric

  3. #3
    Join Date
    Nov 2011
    Posts
    4
    Quote Originally Posted by begooden-it View Post
    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?

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    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

  5. #5
    Join Date
    Nov 2011
    Posts
    4
    Quote Originally Posted by begooden-it View Post
    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??

  6. #6
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    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:

    Code:
    SELECT ... FROM yourtable
    WHERE yourkey IN ( SELECT col FROM yourtemptable )
    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

  7. #7
    Join Date
    Nov 2011
    Posts
    4
    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!

  8. #8
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •