All Forums General
larun1616 29 posts Joined 09/14
15 Sep 2014
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.
 

ulrich 816 posts Joined 09/09
16 Sep 2014

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Raja_KT 1246 posts Joined 07/09
16 Sep 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

larun1616 29 posts Joined 09/14
18 Sep 2014

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?

Raja_KT 1246 posts Joined 07/09
18 Sep 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
18 Sep 2014

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

SAP

You must sign in to leave a comment.