How to use a variable as table name in a procedure 1

[Ino Mart]

All

I am using Pervasive V13 SP2

To simplify my issue hereby an easy description what I want to achieve. So let's assume I currently have a table "Data2020" and a table "Data2021". The structure of both tables is identically so the only difference is that Data2020 contains data of the year 2020 and table data 2021 contains data of year 2021. So to retrieve data I want to create a stored procedure in which I give the table as a parameter

CREATE PROCEDURE Test(in :MyTable nvarchar(20))
returns(
	TheOutput nvarchar(50)
	);
begin
SELECT a. "DateCreated" from :MyTable as a;
end;

However, when I try to save this procedure, I get "Syntax Error: a."DateCreated" from <<???>>? as a"
If I change the select-statement to SELECT a. "DateCreated" from Data2020 as a;, the data is retrieved. So it is clear the error occurs on the :MyTable-variable.

Regards
Ino

So once again: this is a simplified example of what I need. The final query uses multiple tables with similar fieldnames (thus that's why the aliasing in my example above)

 

[mirtheil]

You have to build the SQL statement and then execute it. So, you'll need something like:

CREATE PROCEDURE Test(in :MyTable varchar(20))
returns 
(DateCreated DATE);
BEGIN
  declare :s varchar(8000);
  set :s = 'select a.DateCreated from "' + :MyTable + '" a'; 
  Exec(:s); 
END;

Don't forget to include the RETURNS clause so that you get data back from the Stored Procedure.


Mirtheil

http://www.mirtheil.com

 

[Ino Mart]

Mirtheil

Many thanks. This is indeed what I need.