How to create Firebird SP, get rows from some dynamic tables?

Question

My tables have an internal preliminary fix that users should not know.
So I made a SP like this to get the strings.

CREATE PROCEDURE p_Test  
 ( inStation varchar(10) )  
RETURNS  
 ( ServiceName varchar(20) )  
AS    
DECLARE VARIABLE StationTable varchar(20);  
BEGIN   
  StationTable = 'TSTATION_' || Upper(Trim(inStation));  

  SELECT StationTable.ServiceName  
  FROM StationTable  
  INTO :ServiceName;  
  suspend;  

END^

And I got an error like this

SQL error code = -204
Table unknown
STATIONTABLE

My version of fbclient.dll is 2.1.2.18118.
My test environment is Flamerobin 0.9.2.1851.

Any idea to make this work?

Answer 1

you can try using a run block:

execute block (inStation varchar(10) = ?)
returns (ServiceName = varchar(20))
as
DECLARE variable SQL Varchar(300);
DECLARE VARIABLE StationTable varchar(20);
begin
    StationTable = 'TSTATION_' || Upper(Trim(inStation)); 
    SQL := 'SELECT ' || StationTable ||'.ServiceName FROM ' || StationTable || ' INTO :ServiceName;';
    execute STATEMENT sql;
    SUSPEND;
end

check here

Answer 2

, @Hugues Van Landeghem. , EXECUTE STATEMENT. SP:

CREATE PROCEDURE p_Test ( inStation varchar(10) )
RETURNS ( ServiceName varchar(20) )
AS
    DECLARE VARIABLE StationTable varchar(20);  
BEGIN   
    StationTable = 'TSTATION_' || Upper(Trim(inStation));  
    EXECUTE STATEMENT 'SELECT ServiceName FROM ' || StationTable INTO :ServiceName;
    SUSPEND;  
END^