[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

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.
0
Soumini
Asked:
Soumini
  • 2
1 Solution
 
Nick UpsonPrincipal Operations EngineerCommented:
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, ...)"
0
 
gskoczylasSenior Software DeveloperCommented:
You can use the 'EXECUTE STATEMENT <string>' to execute dynamically created statement (FB 1.5+).
0
 
Nick UpsonPrincipal Operations EngineerCommented:
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

0
 
SouminiAuthor Commented:
Thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now