SQL Script: Select Singel Value for further processing

[Former Member]

Hey guys,

i am trying do write some kind of a dynamic procedure. I created a table which contains properties for some different cases, eg. table names, name of values and other stuff. The plan is to use a input paremeter in my procedure which identifies the properties which should be used. Here is a altered code snipped so you can understand what i am trying to do.

PROCEDURE "anyname" (IN OPTION nvarchar(30),OUT tt_someresults)
LANGUAGE SQLSCRIPT
 SQL SECURITY INVOKER 
 --DEFAULT SCHEMA <default_schema_name>
 READS SQL DATA AS
 -- Define Output Table
BEGIN
/***************************** 
 Write your procedure logic 
 *****************************/
 -- GET responding Table from Properties
valuetable1 = SELECT "TABLE1" FROM "SCHEMA"."PROPERTYTABLE" WHERE "OPTIONNAME" = OPTION;
 
tt_someresults = SELECT "XYZ", "ZYX" FROM "SCHEMA".OPTION WHERE CONDITION;
END;

The first step is to extend the procedure with dynamic table/schema and then for example diferent "SELECTs" and different conditions.

Has anyone an idea if it is possible to do so? It would be create because it would be a modular solution for some queries which would normally use more procedures.

Best Regards,

Lukas

[former_member185132 · Accepted solution]

Hi Lukas,

Try using the EXEC command in SQL, it does what you want. Basically you generate the SQL as a string and pass it to EXEC, which then executes it.

Be aware though, that it also brings with it security and performance issues. That's the trade-off here as this is the only way to dynamically generate SQL.

Regards,

Suhas

[Former Member · Accepted solution]

That is the the approach  which i would use normally, but duo the capabilities of SQL-Script i would like to find a way whis is better in performance.

A work arround would be a big set of input parameters which are filled with the application itself. Like one procedure to get Options and one for actual execution. A wrapper which singel purpose is to tell the database that a result of a query is not a column but a value in a specific datatype which it could use. Here a graphic of the idea:

As already stated, i would rather use advanced logic of SQL-Script itself instead of a wrapper which comes with performance issues etc.

Perhaps you or somebody else has a good approach on this issue

Thanks and Regards Lukas