Ejecutar consulta contenida en string

[[mlara]]

De qué forma se podría ejecutar una consulta contenida en un string?

Ejemplo en procedimiento almacenado:

Código SQL [-]
CREATE PROCEDURE "ExecCustomQuery" (
  in_query VARCHAR(254))
  RETURNS(
  out_done CHAR(1))

AS

DECLARE VARIABLE DebuggerResult INTEGER;

BEGIN

  /* Aquí debo ejecutar la consulta pasada en el parámetro in_query */

  ...

  out_done = '1';

  SUSPEND;
END

Alguien sabe cómo hacerlo? Creo que antes se ha tocado este tema, pero no encuentre algo que me sirviera.

[RolphyReyes]

Saludos.

Busca referencia sobre la sentencia EXECUTE STATEMENT que permite realizar esta operación a partir de FB 1.5.

Extraído del Release v1.5.3:

EXECUTE STATEMENT "string" is a PSQL extension which takes a string that is a valid dynamic

SQL statement and executes it as if it had been submitted to DSQL.
Available in triggers and stored procedures.
Syntax Patterns

The syntax may have three forms.-
Syntax 1
Executes "string> as an SQL operation that does not return any data rows, viz. INSERT, UPDATE,
DELETE, EXECUTE PROCEDURE or any DDL statement except CREATE/DROP
DATABASE.
EXECUTE STATEMENT <string>;
Example

Código SQL [-] CREATE PROCEDURE DynamicSampleOne (Pname VARCHAR(100))
AS DECLARE VARIABLE Sql VARCHAR(1024);
DECLARE VARIABLE Par INT;
BEGIN SELECT MIN(SomeField) FROM SomeTable INTO :Par;
Sql = 'EXECUTE PROCEDURE ' || Pname || '(';
Sql = Sql || CAST(Par AS VARCHAR(20)) || ')';
EXECUTE STATEMENT Sql;
END

SQL Language Enhancements

Syntax 2
Executes "string" as an SQL operation, returning single data row. Only singleton SELECT operators
may be executed with this form of EXECUTE STATEMENT.
EXECUTE STATEMENT <string> INTO :var1, [&, :varn] ;

Example

Código SQL [-] CREATE PROCEDURE DynamicSampleTwo (TableName VARCHAR(100))
AS DECLARE VARIABLE Par INT;
BEGIN EXECUTE STATEMENT
'SELECT MAX(CheckField) FROM ' || TableName INTO :Par;
IF (Par > 100) THEN EXCEPTION Ex_Overflow 'Overflow in ' || TableName;
END Syntax 3 Executes "string" as SQL operation, returning multiple data rows. Any SELECT operator may be
executed with this form of EXECUTE STATEMENT.
FOR EXECUTE STATEMENT INTO :var1, &, :varn
DO ;
Example
CREATE PROCEDURE DynamicSampleThree (
TextField VARCHAR(100),
TableName VARCHAR(100))
RETURNS (Line VARCHAR(32000))
AS DECLARE VARIABLE OneLine VARCHAR(100);
BEGIN Line = '';
FOR EXECUTE STATEMENT
'SELECT ' || TextField || ' FROM ' || TableName
INTO :OneLine
DO IF (OneLine IS NOT NULL) THEN Line = Line || OneLine || ' ';
SUSPEND;
END


Caveats with EXECUTE STATEMENT
The 'EXECUTE STATEMENT' DSQL string cannot contain any parameters in any syntax variation.

All variable substitution into the static part of the SQL statement should be performed before the execution of EXECUTE STATEMENT.

This feature is intended only for very cautious use and should be used with all factors taken into account.

It should be a rule of thumb to use EXECUTE STATEMENT only when other methods are impossible, or perform even worse than EXECUTE STATEMENT.