Row Count from EXECUTE IMMEDIATE statement

I’ve written a few stored procedures for CRUD operations against a table. They all follow the general form:

    update_sql = 'UPDATE table SET column1 = "some value" where id in (1,2,3)' ;
    EXECUTE IMMEDIATE delete_sql ;

However calling these procedures will return a response saying ‘0 row(s) affected’.
Is there some way to get the affected row count from an EXECUTE IMMEDIATE type query?

Something similar to how Oracle does it maybe?

            EXECUTE IMMEDIATE   'BEGIN '
                         || some_sql
                         || '; :rec_count := SQL%ROWCOUNT; COMMIT; END;'
            USING OUT rec_count;

Thanks.

1 Like

Figured it out. The following works for me:

EXECUTE IMMEDIATE CONCAT(update_sql, ' ECHO SELECT row_count() ;') ;

Thanks for sharing that.

Care to expand for an amateur? What does this accomplish?

Sure :smile:.
Some context: I was making these CRUD procedures to be callable from our app front-end through some Python Flask services. I wanted to return the number of rows affected by each operation (specifically create/update/delete statements).
Usually when executing dynamic SQL with that EXECUTE IMMEDIATE statement, I noticed the response was always 0 row(s) affected. This was my work around for that.

1 Like

Actually, we do support getting the row_count() after an EXECUTE IMMEDIATE operation. You don’t have to ECHO SELECT the row_count() from inside the argument to EXECUTE IMMEDIATE. Here’s an example:

insert into t values(1);

delimiter //
create or replace procedure p() returns int
as 
begin
  execute immediate "insert into t select * from t";
  return row_count();
end //
delimiter ;

memsql> echo p();
+--------+
| RESULT |
+--------+
|      1 |
+--------+
1 row in set (0.03 sec)

memsql> echo p();
+--------+
| RESULT |
+--------+
|      2 |
+--------+
1 row in set (0.01 sec)

memsql> echo p();
+--------+
| RESULT |
+--------+
|      4 |
+--------+
1 row in set (0.01 sec)