Subject Re: [firebird-support] Quoted Varchar in Execute Statement?
Author Helen Borrie
At 09:52 PM 24/06/2006, you wrote:
>This is select statement ;
>Select AField from ATable where ADateField=:ADateParam and
>AVCField=:AVCParam into :AResult"
>I want to use it with EXECUTE STAMENT command. I does not run cause of

EXECUTE STATEMENT ('some varchar string') can't take parameters. If
you passed parameters into your SP, they become variables inside the
procedure. You can then use the current value of a variable in
constructing the string for the statement that you want to pass to

>I guess, parameters must be also converted to String??! I following, but it
>doesn't run also!

It is not useful to say "it doesn't run". You should say what
exception you get.

>SQLStr = 'Select AField from ATable where ADateField=' || '''
>cast(:ADateParam as varchar(10)) || ''' AND AVCField = ''' || :AVCParam ||

The main mistake here is using the colon prefix. Leave that out and
concatenate directly the *current value* of the variable. Also make
sure that you concatenate AND double the apostrophes that you include
to delimit the date literal.

SQLStr = 'select AField from ATable ';
SQLStr = SQLStr || 'where ADateField = ' || '' /* doubled apostrophe */ ' ;
SQLStr = SQLStr ||cast (ADateParam as varchar(10)) || '' ';
SQLStr = SQLStr || 'and AVCField = ' || '' || AVCParam || '' /* and
again */ ' ;


Do you do typos like this in your code? A statement like that one
won't run, even if the syntax of the statement string is perfect.

EXECUTE STATEMENT SQLStr into :AResult; /* has the colon because it
is an SQL statement */

Of course, executing this statement will fail with "Multiple rows in
singleton select" if more than one row matches the WHERE arguments....