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

Ino Mart · Jan 6, 2021

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

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)

mirtheil · Jan 6, 2021

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

Code:

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 · Jan 7, 2021

Mirtheil

Many thanks. This is indeed what I need.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

Ino Mart

Programmer

mirtheil

Programmer

Ino Mart

Programmer

Similar threads

Part and Inventory Search

Sponsor