How to make a Firebird SP, get lines from some dynamic TABLES ?

Question

My tables have some inner pre-fix that users must not know.
So I made a SP like this to retrieve lines.

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 a fail message like this

SQL error code = -204
Table unknown
STATIONTABLE

My fbclient.dll version is 2.1.2.18118.
My test enviroment is Flamerobin 0.9.2.1851.

Any idea to make this work?

Answer 1

you can try to use execute 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

Thx for your tips Hugues Van Landeghem. I've made it work using two key words 'EXECUTE STATEMENT'.
Here is my 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^