What is the purpose of using DBC.SYSEXECSQL?

General
Enthusiast

What is the purpose of using DBC.SYSEXECSQL?

Hi

     What is the point in using DBC.SYSEXECSQL('<SQL Query>') when we can execute SQL queries with out using it in STORED PROCEDURE. Does using SYSEXECSQL provide any additional advantage? I am in a fix whether to use it or not. I am not sure if it will create any bottleneck on the system. Please help. THanks.

5 REPLIES
Supporter

Re: What is the purpose of using DBC.SYSEXECSQL?

it is about dynamic SQL.

You can only execute complete SQL's which can be parsed at SP creation time without DBC.SYSEXECSQL.

But sometimes you want to have a SP where you can pass a tablename and run a SQL for this tablename.

That means that durring SP creation time the SQL which will be executed is unknown. In this cases you have to execute the SQL with DBC.SYSEXECSQL...

Ulrich

Enthusiast

Re: What is the purpose of using DBC.SYSEXECSQL?

Thru EXECUTE IMMEDIATE,DBC.SysExecSQL  can we can execute dynamic ddl  statements too. We can stuff logics together to achieve a functionality. Just imagine one call is enough.

Uisng cursors inside a proc can  be slow in performance, when you deal with huge volume of data.

Enthusiast

Re: What is the purpose of using DBC.SYSEXECSQL?

IF we have only SQL Queries to be executed for which i have to pass values dynamically, can it be done with using DBC.SYSEXECSQL? Does usign this have any performance edge or bottleneck?

Enthusiast

Re: What is the purpose of using DBC.SYSEXECSQL?

Ulrich has answered your question above. It is not at all a bottleneck. It is a boon.

SAP
Enthusiast

Re: What is the purpose of using DBC.SYSEXECSQL?

Hi all ,

Please help me on the below things :-

1. I am unable to use EXECUTE IMMEDIATE or even EXECUTE to run Dynmaic SQLs . But  i am able to use DBC.sysexecsql . What s the reason ?

2. However ,  am unable to create any tables inside stored procedure along with other DML operations . But i can create it separately in a stored procedure only with DDL .

3. When i create volatiletable  inside stored proc and have an insert statement and create a proc . It says Volatile table doesnt exist

ex: REPLACE PROCEDURE dfg.sdfffffff

begin 

call dbc.sysexecsql('create volatile ......;');

Insert into volatil values (chsc);

4. Do we really need to use ':' host variables while passing parameters ?

Note : I m using TD 14.01 with TD Studio Express 15 . 

Do i need to do any settings change or ask my DBA to work on something because this s new setup created just few days before ?

Thanks in advance !!!!