Where do I find documentation on parameters to set before invoking the above routine.
Where do I find documentation on what parameters are available to be retrieved?
I would like to submit a query that start with 'Select count(*)' , invoke
and then read a variable that holds the rows counted.
Any suggestions appreciated.
You can read about it in SQL Stored Procedures and Embedded SQL in the Teradata documentation set. However, you cannot execute a select statement via DBC.SysExecSQL. A dynamic cursor, as illustrated in the example below, is probably the solution for your requirement.
CREATE PROCEDURE GetEmployeeSalary
(IN EmpName VARCHAR(100), OUT Salary DEC(10,2))
DECLARE SqlStr VARCHAR(1000);
DECLARE C1 CURSOR FOR S1;
SET SqlStr = 'SELECT Salary FROM EmployeeTable WHERE EmpName = ?';
PREPARE S1 FROM SqlStr;
OPEN C1 USING EmpName;
FETCH C1 INTO Salary;
There is not any material that covers setting / retrieving parameters in indicated manual.
There is only one example - very minimal documentation.
All the documentation is in the file mentionned by above by Jim.
You cannot set or retrieve parameters (or result sets) using SysExecSQL(), by definition, this is only invoquing the SQL statement you pass to it. You can use [volatile/temporary] talbles to pass and retrieve data with the embedded code.
That said, if you are just looking to retrieve a count, you are probably looking in the wring direction and won't need dynamic SQL for it...
DBC.SysExecSQL is a kind of dummy/placeholder, you can use it within a SP, but it doesn't exist, it never did :-)
In newer releases you should use EXECUTE IMMEDIATE instead.
i'm currently having a problem. not sure if this is a teradata bug or something
I'm running a dynamic insert sql (complete with generating a where clause based on values). The thing is, i'm calling DBC.SysExecSQL and getting ACTIVITY_COUNT to get the number of rows inserted. It was working fine for some records but for other records, the ACTIVITY COUNT is null ?
So, what i did was to put a table, log the value from the ACTIVITY_COUNT to this and when i executed the procedure and checked the table for logs, it came out around 5 records. I'm expecting only 1 record with a value of 990, however, the table which i placed some logs is giving me 5 records like
when you add them up, it doesn't add up to 990. I tried using EXECUTE IMMEDIATE, however, the same thing. What I plan to do, is to put the records into a volatile table and generate the count from those records. It's going to be a two step approach, I was wondering if there's a way to return only one record with 990 instead of those multiple records having different numbers. I tried searching the web for answers but couldn't find one, hope you can help me on this matter.
I want to use ACTIVITY_COUNT with DBC.SysExecSQL to get how many rows updated by query executed in DBC.SysExecSQL.
Could you please help me in this ..