CODEWITHSUNDEEP
sqlhana
Chip Allen
Chip Allen

Reputation: 290

How to return the number of affected rows in a HANA stored procedure?

How does one return the number of rows affected from an insert or update statement while inside a SAP HANA stored procedure?

In oracle one would use sql%rowcount but I can't find an equivalent for HANA in their documentation?

For example:

CREATE PROCEDURE procedure_name (p_input)
LANGUAGE SQLSCRIPT AS
BEGIN
   define c integer;
   insert into some_table values (somevalues);
   c := sql%rowcount;
END

UPDATE:

I found the answer on an SAP thread finally. You can run this statement after the insert or update to get the rowcount:

SELECT ::ROWCOUNT into L_C FROM DUMMY;

Upvotes: 11

Views: 17182

Answers (2)

pawelofficial
pawelofficial

Reputation: 367

Isn't there a problem with ::ROWCOUNT since it is a system variable, so using it by several sessions simultaneously could produce incorrect results?

Upvotes: 0

Peder Rice
Peder Rice

Reputation: 1794

Not trying to steal internet points, but this should have an answer that's not just in the description of the question. To select row count, use the following:

SELECT ::ROWCOUNT INTO someVariable FROM DUMMY;

DUMMY is a special keyword in HANA and is used to select variables vs selecting from a table. You cannot simply SELECT ::ROWCOUNT INTO someVariable in HANA.

Upvotes: 9

Related Questions