Subject Re: [firebird-support] Dynamic Query in Firebird
Author Martijn Tonies
Hello Tomy,

> CREATE PROCEDURE SPDYNAMICQUERY (sqlquery varchar(1024))
> returns (outputvalue varchar(1024)) <= how to make this parameter
> as
> declare variable sql varchar(1024);
> begin
> execute statement :sqlquery into :outputvalue; <= this valid for 1 return
value only
> end^
> The main purpose of the stored procedure is execute a SQL query. We have
several stored procedures and our framework will execute them by passing
> e.g.
> Query 1 : Select users.userid, users.username from users
> Query 2 : Select accounts.accountid, accounts.balance, accounts.isexpired
from accounts

This is where things go wrong -- Firebird is expecting 1 output value, not
cause you only have one parameter in the "INTO" clause.

> NOTE that query1 returns 2 values (int, string) and query 2 returns 3
values (int, money, boolean). SPDYNAMICQUERY doesn't know how many input
parameter and its datatypes.
> Are there any approach for this problem using Firebird Stored Procedure?

Not if you want a dynamic number of output parameters.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
My thoughts:
Database development questions? Check the forum!