How to use a variable as table name in a procedure
How to use a variable as table name in a procedure
(OP)
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
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)
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
CODE -->
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)
RE: How to use a variable as table name in a procedure
CODE
Don't forget to include the RETURNS clause so that you get data back from the Stored Procedure.
Mirtheil
http://www.mirtheil.com
RE: How to use a variable as table name in a procedure
Many thanks. This is indeed what I need.