Hello Tomy,

> CREATE PROCEDURE SPDYNAMICQUERY (sqlquery varchar(1024))
> returns (outputvalue varchar(1024)) <= how to make this parameter

dynamic?

> 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
into SPDYNAMICQUERY.

> 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
multiple,
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
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com