How to use a variable as a select for Columns

[Former Member]

Hi,

we are students and currently trying to execute a procedure which involves using a variable to select a specific column out of a given table. We use the variable "table_name" to store the String value for the column that we need to select. The code is as following:

CREATE PROCEDURE "MY_DIRECTORY"."test_procedure" (IN table_name NVARCHAR(30))

LANGUAGE SQLSCRIPT AS

BEGIN

     DECLARE temp DOUBLE := 0;

.......

        SELECT :table_name INTO temp FROM "MY_DIRECTORY"."test" WHERE "DATUM" = '2015-03-02 00:31:00';

.......

END;

call "MY_DIRECTORY"."test_procedure" ('VSA191_D1_T1_ZAEHL');

Is there any chance that this code is executable by useing a workaround? We would be very glad for your support.

Kai

[pfefferf · Accepted solution]

Hello Kai,

the need for that requirement is not really clear to me, but I think you are just trying out some things .

You can use Dynamic SQL statement EXEC for that in combination with a global temporary table. You have to build a statement that inserts the selected results into the global temporary table. This is necessary, because within dynamic sql you cannot address any SQLScript variables.

But please consider the notes for the dynamic sql regarding security and performance.

I would not recommend to use such an approach for a productive scenario. Trying it out and deleting it afterwards is ok .

Best Regards,

Florian

[Former Member · Accepted solution]

Hi Florian,

thanks a lot for your answer.

we have solved the issue using dynamic sql and a little workaround.

Kai