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!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Ino Mart

Programmer
Jan 6, 2021
2
BE
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)
 
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
 
Mirtheil

Many thanks. This is indeed what I need.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top