At 03:11 AM 9/04/2006, you wrote:

>-----Original Message-----
>
>I want to create generic procedure to execute sql statements passed as
>variable.
>I have made this test procedure to verify if is working:
>
>CREATE PROCEDURE T2 (
> T VARCHAR(20))
>RETURNS (
> IDN VARCHAR(30),
> IDL INTEGER)
>AS
>DECLARE VARIABLE STRSQL VARCHAR(50);
>begin
> strsql='select loc_id, loc_name from '||T;
> for execute statement :strsql into :idl, :idn do
> begin
> suspend;
> end
>
>end
>
>I pass table name (SYS_LOCATION) and I get this error message:
>"Variable type (position 0) in EXECUTE STATEMENT 'select loc_id, loc_name
>from SY' INTO does not match returned column type.
>Password required"
>If I change strsql='select loc_id, loc_name from '||T||' '; I get message:
>"Variable type (position 1) in EXECUTE ......"
>Same message if I modify like this:
>strsql='select loc_id, loc_name from ';
>for execute statement :strsql ||T into :idl, :idn do
>
>Error message:
>ISC ERROR CODE:335544829
>
>ISC ERROR MESSAGE:
>Variable type (position 1) in EXECUTE STATEMENT 'select loc_id, loc_name
>from SY' INTO does not match returned column type
>password required

1. Ignore the procedure debugger, it's only as good as its local
parser can make it and that, in turn, is only as good as what the
parser author designed.

2. You have at least three errors here.

a) misdeclaration of the input variable for the table
identifier. Identifiers are char(31). Varchar(31) would be
OK; varchar(20) is not, so this is the cause of the type mismatch.

b) the variable for the input string is probably too short. You
don't have to be mean with this - make it at least as long as the
static byte count + 32.

c) considering that you have mismatched lengths in both of those
areas, review the definitions of your output variables, e.g. if the
data for loc_id is BigInt, there will be an overflow in idl, and if
the defined length of loc_name is longer than 30, you will get an
overflow there.

./hb