CODEWITHSUNDEEP
sqlstored-proceduresrowsteradata
Rocketq
Rocketq

Reputation: 5791

How to get number of rows affected by last several sql statement inside procedure in teradata?

So let I have 3 merge statement inside the procedure:

CREATE PROCEDURE SP_Employee(OUT num_rows BIGINT)
BEGIN
    MERGE INTO t1
         USING t2
    ...;
    MERGE INTO a1
         USING b2
    ...;
    MERGE INTO c1
         USING d2
    ...;
END;

How can I get number of rows affected inside procedure? I know about ACTIVE_COUNT, but can I use it? Is that num_rows = ACTIVE_COUNT will work, or need to add it to variable several times and initially set to 0?

Upvotes: 0

Views: 1854

Answers (2)

dnoeth
dnoeth

Reputation: 60502

You need to initialize a variable to zero and then add the count (Either using ACTIVITY_COUNT or the GET DIAGNOSTICS ROW_COUNT) after each execution of a DML statement:

CREATE PROCEDURE SP_Employee(OUT num_rows BIGINT)
BEGIN
   DECLARE ac BIGINT DEFAULT 0;

    MERGE INTO t1
         USING t2
    ...;
   SET ac = ac + ACTIVITY_COUNT;

    MERGE INTO a1
         USING b2
    ...;
   SET ac = ac + ACTIVITY_COUNT;

    MERGE INTO c1
         USING d2
    ...;
   SET ac = ac + ACTIVITY_COUNT;

   SET num_rows = ac;

END;

Upvotes: 2

Aritra Bhattacharya
Aritra Bhattacharya

Reputation: 780

You can insert the below piece after each of your merge.

Merge INTO t1
      USING t2
.....;

SET lv_activity_count = activity_count;


SET lv_message = ' Number of rows merged in table1 is  '|| lv_activity_count ;
..

Please initialise and declare the variables as well.

Upvotes: 1

Related Questions