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

asked 05 Jun, 05:55

mfkpie8's gravatar image

mfkpie8
228556266
accept rate: 14%


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

permanent link

answered 05 Jun, 08:02

Volker%20Barth's gravatar image

Volker Barth
37.6k343512778
accept rate: 34%

edited 05 Jun, 12:56

Please accept my best thanks!

(07 Jun, 11:26) mfkpie8
1

@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]';

(08 Jun, 02:49) Baron
Replies hidden

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/ce224c029e8c482c9fb637f5df3d43e4.html:

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.

(08 Jun, 07:05) Breck Carter

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

SELECT * FROM `[t_owner]`.`[t_name]`;
(08 Jun, 07:05) Volker Barth

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!!

(08 Jun, 07:10) Baron

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 ~).

(08 Jun, 07:10) Chris Keating
Replies hidden

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?)

(08 Jun, 07:12) Volker Barth
2

(see the key that has the tilde ~)

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

(08 Jun, 07:15) Volker Barth

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.

(08 Jun, 11:35) Breck Carter

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.

(08 Jun, 12:35) Volker Barth

> non-English keyboard

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

(09 Jun, 08:37) Breck Carter
showing 4 of 11 show all flat view

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

permanent link

answered 05 Jun, 06:34

Justin%20Willey's gravatar image

Justin Willey
7.4k128172242
accept rate: 20%

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')

(06 Jun, 16:46) Baron
Replies hidden
2

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

(07 Jun, 01:32) Volker Barth
(07 Jun, 04:26) Baron
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×118

question asked: 05 Jun, 05:55

question was seen: 119 times

last updated: 09 Jun, 08:40