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.