asked on

Equivalent of sp_executesql in FireBird

Is there any statement in Firebird that can be used as an equivalent of sp_executesql (MSSqlServer) ? I need to select some fields based on a condition and that condition will be known only at run time. so i cannot hard code the "Where" clause. I can use the front end language to
execute the statement. But the statement might fetch a large number of Rows. I feel it will be better if it was done thru stored procedures.
But I dont know if Firebird supports executing a made up query.

Nick Upson

you have to write the SP correctly

something like:

for select ... where x = :param1 ... into
do
begin
suspend;
end

the suspend says "return the current result set"

you then call it in the same way as a table, only passing params "select a,b,.. from my_sp(param1, param2, ...)"

ASKER CERTIFIED SOLUTION

gskoczylas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nick Upson

Here is a real SP, just call as "select * from PR_TEST_VIEW_RESULTS_PH1 "

CREATE OR ALTER PROCEDURE PR_TEST_VIEW_RESULTS_PH1 
returns (D1      Integer,
         OSIDENT Integer,
         D3      Integer,
         D4      Integer,
         D5      Integer,
         D6      Integer,
         D7      Integer,
         D8      Integer,
         D9      Integer,
         D10     Integer,
         D11     Integer,
         D12     Integer,
         D13     Integer,
         D14     Integer,
         D15     Integer,
         D16     Integer,
         D17     Integer,
         D18     Integer,
         D19     Integer,
         D20     Integer,
         D21     Integer,
         RSSI    Smallint)
AS 
  declare variable src CHAR(3);
  declare variable msg SMALLINT;
begin
/* extract rssi data for path loss analysis
*/
 
d1 = 0;
d3 = 0;
d4 = 0;
d5 = 0;
d6 = 0;
d7 = 0;
d8 = 0;
d9 = 0;
d10 = 0;
d11 = 0;
d12 = 0;
d13 = 0;
d14 = 0;
d15 = 0;
d16 = 0;
d17 = 0;
d18 = 0;
d19 = 0;
d20 = 0;
d21 = 0;
         
for select osident, rssi, src, msg from TBLSYSMSGLOG 
where rssi != 0 
order by ID
into :osident, :rssi, src, msg
do
begin
    if (src = 'RXC' and msg = 6) then
        rssi = 0;
    if (rssi != 0) then
        suspend;
end
end

Open in new window

Soumini

ASKER

Thanks