Need to save and execute a dynamically generated query(greater than 32K bytes) from a Procedure

Database

Need to save and execute a dynamically generated query(greater than 32K bytes) from a Procedure

Hi All

I have a procedure which dynamically generates queries. However in some cases the generated query is exceeding the maximum size of a VARCHAR due to which the variable holding the query in the procedure truncates the query.

Also, I am using dbc.SysExecSQL(sqlStmt) procedure for executing queries.

Can I use CLOB. If yes then will it be executable using this procedure or can I append CLOB with another CLOB.

If No, Please suggest some solution

Thanks

1 REPLY
Fan

Re: Need to save and execute a dynamically generated query(greater than 32K bytes) from a Procedure

Hi Ankit,

In TD14 Maximum size of a sql can be upto 64K.

Try using below declaration

declare var1 Varchar(45000);

declare var2 Varchar(15000);

The problem will araise If the var1 variable is derived from appending another variable var2 sum of all the variable size should be below 64K.

SET var1 = var1 ||','|| var2;

Hope this may help you

Thanks,

Shan