How to make the table name passed as a variable in the stored procedure of the database

[ximen]

CREATE PROCEDURE "DBA"."Z_calltable"(  IN  
@table_name char(128)

)
 RESULT( "column_name" char(128) ) 
BEGIN
 select  * from  @table_name
END;

How to make the table name passed as a variable in the stored procedure of the database

[VolkerBarth · Accepted solution]

With v17, use indirect identifiers and/or TABLE REFs. They are precisely meant for such tasks to avoid the need for EXECUTE IMMEDIATE.

[ximen · Accepted solution]

Please accept my best thanks!

[Baron · Accepted solution]

@Volker Barth, thanks for the hint of indirect identifierst, but I get a syntax error while executing this code (SQL Anywhere 17):

CREATE OR REPLACE VARIABLE t_owner LONG VARCHAR = 'GROUPO';

CREATE OR REPLACE VARIABLE t_name LONG VARCHAR = 'Employees';

SELECT * FROM '[t_owner]'.'[t_name]';

[Breck_Carter · Accepted solution]

Use back quotes like it says in the SAP Help Portal.

CREATE OR REPLACE VARIABLE t_owner LONG VARCHAR = 'GROUPO';
CREATE OR REPLACE VARIABLE t_name LONG VARCHAR = 'Employees';
SELECT * FROM `[t_owner]`.`[t_name]`;

IMO it's time to stop using the HTML Help because it's wrong wrong wrong:

Specify indirect identifiers in statements by enclosing them in square brackets and back quotes (for example, '[@myVariable]'), ...

Note that it SAYS back quotes BUT USES ordinary single quotes in all the example code.

The SAP Help Portal GETS IT RIGHT https://help.sap.com/viewer/93079d4ba8e44920ae63ffb4def91f5b/17.0/en-US/ce224c029e8c482c9fb637f5df3d...:

Specify indirect identifiers in statements by enclosing them in square brackets and back quotes (for example, `[@myVariable]`), ...

FWIW DCX is saying "This site can’t be reached dcx.sap.com took too long to respond."

IMO SAP should retire DCX and apply the savings to other areas of SQL Anywhere.

[VolkerBarth · Accepted solution]

Note the according DCX comment, indirect identiers require back quotes, not apostrophes:

SELECT * FROM `[t_owner]`.`[t_name]`;

[Baron · Accepted solution]

Oh, thank you. It works now!!

The snippet I have copied from http://dcx.sap.com/ but did not read the explanation of the syntax!

Yes HTML sometimes spoins codes!!

[chris_keating · Accepted solution]

You must specify indirect identifiers in statements by enclosing them in square brackets and back quotes. The backquote is ` (see the key that has the tilde ~).

[VolkerBarth · Accepted solution]

It's time to stop using the HTML Help because it's wrong wrong wrong:

Well, I don't know whether DCX is still actively used by the SQL Anywhere Doc Team – particularly by Jack –, but then again, corrections are explained there as comment, how do you get them within the SAP Help Portal for corrections after 17.0.4? (In other words: Do you know the release of the SQL Anywhere Help?)

[VolkerBarth · Accepted solution]

(see the key that has the tilde ~)

FWIW, that's not necessarily true for non-English keyboard layouts...

[Breck_Carter · Accepted solution]

By "HTML Help" I mean SQL-Anywhere-17-en.chm not DCX.

I can't get DCX to work any more, unless I use a browser that will accept an explicit http: in the URL. Chrome doesn't, and https://dcx.sap.com times out... for me 🙂

> Do you know the release of the SQL Anywhere Help?

No, but I'm pretty sure there's nothing more up to date than the SAP Help portal. The PDF cover pages I checked say Document Version: 17 – 2020-12-11 which is four years more recent than 17.0.4 🙂

The bottom line is, maintaining DCX is a complete and absolute waste of valuable resources. The world doesn't need two different websites describing exactly the same product.

[VolkerBarth · Accepted solution]

Ah, I misread about HTML help.

Needless to say, I hope DCX stays in place as important doc resource for older versions, as these are actually still in use.

[Breck_Carter · Accepted solution]

> non-English keyboard

Perhaps it was designed for the Elbonian keyboard where the back quote can be any key you want it to be 🙂

[justin_willey]

Use EXECUTE IMMEDIATE but you may need to protect against SQL injection

[Baron]

execute immediate doesn't work if the executed statement yields a result set.

For example this one doesn't work: execute immediate ('select * from employee')

but this one works: execute immediate ('delete from employee')

[VolkerBarth]

Add the WITH RESULT SET ON clause, and it certainly does work, so your claim is not correct.

[Baron]

Thanks @volker Barth