SSC-EWI-TD0023

ACTIVITY_COUNT inside SELECT/SET INTO VARIABLE requires manual fix

Severity

Low

Description

The ACTIVITY_COUNT status variable returns the number of rows affected by an SQL DML statement in an embedded SQL or stored procedure application. For more information check here.

As explained in its translation specification, there is a workaround to emulate ACTIVITY_COUNT's behavior through:

SELECT $1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

When using ACTIVITY_COUNT in a SELECT/SET INTO VARIABLE statement, it can not be simply replaced by the workaround mentioned above.

Example Code

Teradata

IN -> Teradata_01.sql

REPLACE PROCEDURE InsertEmployeeSalaryAndLog_4 ()
BEGIN
    DECLARE rowCount INT;
    DECLARE message VARCHAR(100);

    INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
    VALUES (101, 'Alice', 'Smith', 10, 70000.00);

    SELECT ACTIVITY_COUNT INTO rowCount;
    SET message = 'ROWS INSERTED: ' || rowCount;

    -- Insert the ACTIVITY_COUNT into the activity_log table
    INSERT INTO activity_log (operation, row_count)
    VALUES (message, rowCount);
END;

Snowflake

OUT -> Teradata_01.sql

CREATE OR REPLACE PROCEDURE InsertEmployeeSalaryAndLog_4 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/15/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
               rowCount INT;
               message VARCHAR(100);
    BEGIN
                
                
               INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
               VALUES (101, 'Alice', 'Smith', 10, 70000.00);
               SELECT
            ACTIVITY_COUNT !!!RESOLVE EWI!!! /*** SSC-EWI-TD0023 - ACTIVITY_COUNT INSIDE SELECT/SET INTO VARIABLE REQUIRES MANUAL FIX ***/!!! INTO
            :rowCount;
            message := 'ROWS INSERTED: ' || rowCount;

            -- Insert the ACTIVITY_COUNT into the activity_log table
            INSERT INTO activity_log (operation, row_count)
            VALUES (:message, :rowCount);
    END;
$$;

Manual Fix

Part of the workaround presented above can be used to still get the number of rows inserted/updated/deleted like this:

CREATE OR REPLACE PROCEDURE InsertEmployeeSalaryAndLog_4 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/15/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
               rowCount INT;
               message VARCHAR(100);
    BEGIN
                
                
               INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
               VALUES (101, 'Alice', 'Smith', 10, 70000.00);
               SELECT $1 INTO :rowCount FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
            message := 'ROWS INSERTED: ' || rowCount;

            -- Insert the ACTIVITY_COUNT into the activity_log table
            INSERT INTO activity_log (operation, row_count)
            VALUES (:message, :rowCount);
    END;
$$;

Instead of using the complete query, it needs to be adapted manually to Snowflake's SELECT INTO VARIABLE syntax.

Furthermore, if RESULT_SCAN(LAST_QUERY_ID()) is giving incorrect results, check SSC-FDM-TD0033 for how to handle possible limitations of using LAST_QUERY_ID.

Recommendations

• Manually adapt the proposed workaround.

• Check SSC-FDM-TD0033 for how to handle possible limitations of using LAST_QUERY_ID.

• If you need more support, you can email us at [e-mail]