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

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?


Thanks


________________________________

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



Hello Tomy,

> How to create a stored procedure that receive sql query as input parameter

and return values based on its input?

>
> The skeleton for the stored procedure is:
>
> create procedure spDynamicQuery(sqlQuery varchar(1024))
> as
> begin
> -- execute statement :sqlQuery here
> end
>
> example of sqlQuery: 'select users.username from users where users.userid

= ''John'''

>
> I've tried many approaches but there's an error message (Wrong request

type in EXECUTE STATEMENT).

EXECUTE STATEMENT is how you do it.

But, if you do a SELECT, it needs something to return values to. You cannot
"just select" and expect the results to go "somewhere".

Show us your exact procedure.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
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]