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