10

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;
Chip Allen
  • 280
  • 1
  • 2
  • 12

2 Answers2

8

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.

Peder Rice
  • 1,764
  • 3
  • 28
  • 51
  • 1
    `DUMMY` is not a keyword, but a pre-delivered auxiliary table. It's used to produce a single tuple, pretty much the same way, Oracle's `DUAL` table is used. With current versions (HANA 1 >=SP12 and HANA 2), the use of `DUMMY` in SQLScript can often be avoided by a simple assignment, e.g. `i = ::ROWCOUNT`; – Lars Br. Oct 12 '17 at 06:26
0

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

pawelek69420
  • 134
  • 1
  • 5
  • This isn’t an answer but an additional question - should probably be a comment... anyhow, while `::ROWCOUNT` is a system variable it doesn’t imply that it’s just the same variable value across the system. That would be pretty useless wouldn’t it? Instead it’s value is session local. So your session will see different values than other sessions. – Lars Br. Apr 01 '20 at 09:02
  • i don't have enough reputation for commenting unfortunately. Thanks for clarification! – pawelek69420 Apr 01 '20 at 09:10