Retrieving metadata about most recent query

Database
Fan

Retrieving metadata about most recent query

Hello

In my stored procedures, I'm looking to start recording an "audit log" after each step.

So for example:

/*
* Step 1 in my Stored Proc
*/

DELETE FROM MyDatabase.MyTable_Step1 ALL;

INSERT INTO MyDatabase.MyTable_Step1
SELECT blah;

/*
* Insert Audit Log for the above step
*/

INSERT INTO MyDatabase.AuditLog(SPName, Step, Timestmp, RowsImpacted)
VALUES('MySP', 'Step 1', CURRENT_TIMESTAMMP, CountRowsImpacted() );

/*
* Step 2 in my Stored Proc
*/

UPDATE MyDatabase.MyTable_Step1
FROM ( SELECT 'blah' AS blah ) AS a
SET Col = a.blah
WHERE Age < 30;

/*
* Insert Audit Log for the above step
*/

INSERT INTO MyDatabase.AuditLog(SPName, Step, Timestmp, RowsImpacted)
VALUES('MySP', 'Step 2', CURRENT_TIMESTAMMP, CountRowsImpacted() );

The big I'm interested in is where I've written CountRowsImpacted() above. Obviously I've made that up and it's not a built-in Teradata function, but it explains what I'm looking to do.

Does anyone have any suggestions about how to achieve this result?

Kind regards
Tim
1 REPLY
Enthusiast

Re: Retrieving metadata about most recent query

Use the "ACTIVITY_COUNT" status variable after a DML statement.

Pay attention that it is an INTEGER typed variable, limited to values up to 2^32-1