Subject RE: [firebird-support] Dynamic Query in Firebird
Author Tomy Handaka
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: on behalf of Martijn Tonies
Sent: Wed 5/23/2007 3:23 PM
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
> 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! <>

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