CODEWITHSUNDEEP
selectstored-procedureshanasap-cloud-platform
DOUGLAS ARAUJO
DOUGLAS ARAUJO

Reputation: 1

Hana - Select without Cursor

I am doing a select in a stored procedure with a cursor, I would to know If I could do the same select without using a cursor.

PROCEDURE "DOUGLAS"."tmp.douglas.yahoo::testando" ( ) 
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER 
  DEFAULT SCHEMA DOUGLAS
  AS
BEGIN
/***************************** 
  procedure logic 
 *****************************/

declare R varchar(50);

declare cursor users FOR select * from USERS WHERE CREATE_TIME between ADD_SECONDS (CURRENT_TIMESTAMP , -7200 ) and CURRENT_TIMESTAMP;

FOR R AS users DO
  CALL _SYS_REPO.GRANT_ACTIVATED_ROLE('dux.health.model.roles::finalUser',R.USER_NAME);

  END FOR;

END;

Upvotes: 0

Views: 3640

Answers (1)

Lars Br.
Lars Br.

Reputation: 10388

Technically you could convert the result set into an ARRAY and then loop over the array - but for what?

The main problem is that you want to automatically grant permissions on any users that match your time based WHERE condition. This is not a good idea in most scenarios.

The point of avoiding cursors is to allow the DBMS to optimize SQL commands. Telling the DBMS what data you want, not how to produce it.

In this example it really wouldn't make any difference performance wise.

A much more important factor is that you run SELECT * even though you only need the USER_NAME and that your R variable is declared as VARCHAR(50) (which is wrong if you wanted to store the USER_NAME in it) but never actually used. The R variable in the FOR loop exists in a different validity context and actually contains the current row of the cursor.

Upvotes: 1

Related Questions