Skip to Content
0
Apr 30, 2015 at 02:01 PM

Create Procedure (Select and Call Procedure)

20 Views

Hi Team,

I am creating a procedure that collect a select in a variable and send it to another procedure as parameter. But in the loop(FOR) I am getting some troubles, I have to set a role with a procedure in every user that will be created.

The procedure code is this:

PROCEDURE "DOUGLAS"."tmp.douglas.yahoo::testando" ( )

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

DEFAULT SCHEMA DOUGLAS

--READS SQL DATA

AS

BEGIN

/*****************************

procedure logic

*****************************/

declare role varchar;

declare i integer;

declare cont int default 1;

role = 'dux.health.model.roles::finalUser';

users = select USER_NAME from USERS WHERE CREATE_TIME between ADD_SECONDS (CURRENT_TIMESTAMP , -7200 ) and CURRENT_TIMESTAMP;

userscount = select count (USER_NAME) from USERS WHERE CREATE_TIME between ADD_SECONDS (CURRENT_TIMESTAMP , -7200 ) and CURRENT_TIMESTAMP;

FOR i in 1..cont DO

CALL _SYS_REPO.GRANT_ACTIVATED_ROLE(:role,users);

i := i + 1;

END FOR;

END;

Thank's;