Yeah, that is the main problem. I have to define [an exact number of] output variables in stored procedure, but it have to be dynamic since the stored procedure can receive ANY sql query.

How to make this 'return values' dinamic, based on SQL query in its input?

Any suggestions?

________________________________

From: firebird-support@yahoogroups.com on behalf of Martijn Tonies
Sent: Wed 5/23/2007 3:23 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Dynamic Query in Firebird



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






[Non-text portions of this message have been removed]