Skip to Content
0

SQL Dynamic execution best practices

Apr 13, 2017 at 05:08 AM

39

avatar image

Tables in SCHEMA_A:

Table_A, Table_B, Table_C

Problem statement:

Achieving dynamic selection of tables and columns (within the selected table) to perform manipulations such as joins, calculated columns, etc. and then expose the final result set as ODATA.

Eg:

If the user selects TABLE_A and COLUMN_1 (name of the column in TABLE_A)from UI, the code (in the backend) should pick TABLE_A and fetch the COLUMN_1 values and carry on with the remaining logic(which is same across all the three tables and columns).

Next, the user chooses TABLE_B and Column_2 (Name of the column in TABLE_B), the same logic should be executed but the values are to be picked up from TABLE_B->Column_2.

After the execution of the logic, the final result set has to be passed to UI as ODATA service.

Approaches tried:

1.Using Procedure (With EXEC):

Sample Code:

PROCEDURE "SCHEMA_ABC"."dynamic_test " ( IN t_column NVARCHAR(30), IN t_table Nvarchar(80))

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

AS

BEGIN

exec ' select now(),"'||:t_column||'" from "SCHEMA_ABC"."'||:t_table'" ';

END

Problem:

To go ahead with the business logic to be applied on the data, I am unable to get the result set assigned to a table variable as below,

Tab_Var=exec ' select now(),"'||:t_column||'" from "SCHEMA_ABC"."'||:t_table||'" ';

2.Query the table and column from input variables of procedure/scripted Calc. View:

Eg:

VAR_A=SELECT :t_column from :t_table;

This approach assumes “ :IP_TABLE “ as a physical table and it doesn’t take the value of the INPUT variable to be considered as table name. Same happens with IP_COLUMN as well.

3.Using a temporary table.

exec ' INSERT INTO “SCHEMA_ABC”.”TEMPORARY_TABLE” (select now(),"'||:t_column||'" from "SCHEMA_ABC"."'||:t_table||'") '

The problem here is, after each request from UI, the procedure should be executed, then the data from the temporary table has to be exposed to UI. When there are multiple requests from different users (requesting for different TABLES and COLUMNS), this approach fails as it caters only to one request at a time (else the data is inconsistent).

Expectation :

I would like to have a best practice or a solution to the above mentioned issue.

Thanks in advance

Regards

Srivatsan

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers