Row Count from EXECUTE IMMEDIATE statement

Help Procedural SQL
1

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
2

Figured it out. The following works for me:

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

Thanks for sharing that.

4

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

5

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
6

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)
7

Follow up question @hanson
I have parent and child procedure ,I have to do some upadtes based upon child procedure row count. Value.
I am getting 0 row count from parent proc after calling child …

Pls suggest??

8

I’m not sure I fully understand the scenario. Can you show a repro? If it is different from the original topic, please open a new item.

9

@hanson it is similar to this thread.
Pls see below code sample…

Create proc Parent ()
Begin
#Inside this proc calling child proc where some data insertion happening
Call child();
If row_count() >O then
#based on this count we need to update data in X table
End if;

End;

But in above scenario child procedure is always returning 0 row_count(),even rows inserted in associated table via child procedure.
Is memsql supporting row_count() in nested procedure call??

10

It seems like we should support row_count after a return from a procedure, but apparently it’s not working that way based on your description. I’ll bring this to the attention of our dev team. I would not call it a “bug” though; just the current behavior. For now, I’d recommend getting the row_count() in the child procedure and returning to the caller.

1 Like
11

Thank you @hanson for quick response
Actually we are doing migration and we don’t want to do proc structure changes as multiple application consuming proc, is there any better alternative of return?
I thought for echo select with void return type but don’t know how to get count value in parent procedure as type is void. …
Please suggest

12

Well, I think if you make the proc return and INT with the row count that callers that were expecting void won’t break, they will just ignore it.

Or, you could put the number in a temp table or regular table and let the caller grab it from there (but watch out for concurrency issues there; make sure there’s a unique handle for the result or otherwise no concurrency conflict).

1 Like