Teradata stored procedure activity count

Activity_COUNT is used in Teradata Stored Procedure to keep track of the number of records impacted by any SQL Query. To check it, just use the ACTIVITY_COUNT keyword after any SQL Query which impacts rows. It is initialised to 0 at the start and value is updated after every single SQL query. Lets see an example below to see how Activity_Count works in Teradata:

REPLACE PROCEDURE SP_ACTIVITYCOUNT (OUT tot_count_delete INTEGER)
SQL SECURITY INVOKER
BEGIN
CREATE MULTISET VOLATILE TABLE vt_test1
(
name VARCHAR(20)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt_test1 VALUES ('Mike');
INSERT INTO vt_test1 VALUES ('Megan');
INSERT INTO vt_test1 VALUES ('Morgan');
INSERT INTO vt_test1 VALUES ('Mona');

DELETE FROM vt_test1 WHERE name LIKE 'M%n';
SET tot_count_delete = ACTIVITY_COUNT;
END;

CALL SP_ACTIVITYCOUNT (tot_count_delete);

Output: 2 [ Because Megan and Morgan were deleted ]

Activity_Count is very useful especially for logical decisions and in debugging.

Leave a Reply

Your email address will not be published. Required fields are marked *