cancel
Showing results for 
Search instead for 
Did you mean: 

Mass User Change

martin_chambers
Participant
0 Kudos

I would like to change all the users. Specifically, I would like to add the email address as a parameter. For one user at a time I can do this using ALTER USER. But for a mass user change, I decided I would need a complete procedure using the cursor. To that purpose I uploaded the user names and email addresses to a table I created: user_email.

CREATE PROCEDURE user_email()

LANGUAGE SQLSCRIPT AS

BEGIN

    DECLARE v_email   NVARCHAR(256) default '';

    DECLARE v_user    NVARCHAR(5000) default '';

    DECLARE CURSOR c_1  FOR

                   SELECT user, email FROM MYSCHEMA.USER_EMAIL;

    FOR cur_row AS c_1

               DO

        v_user = cur_row.user;

        ALTER USER v_user

                              SET PARAMETER

                              email address = cur_row.email;

    END FOR;

END;

Unfortunately, this does not work. I get a syntax error for the ALTER USER line  "USER is not supported".

This has left me baffeled. Any help will be much appreciated.

Cheers,

Martin

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

The ALTER statements cannot be run directly from within SQLScript.

To do that, you need to use dynamic SQL ( EXEC/EXECUTE IMMEDIATE).

martin_chambers
Participant
0 Kudos

Hi Lars,

I tried dynamic SQL and in the end I got it to work (see below). But I think, I don't like all those apostrophes and concatentations very much.

Is it possible to create another procedure to contain the ALTER USER bit and call the second procedure in the first one? I tried. But I got an error message. Somthing about DDL Statements not being supported.

Regards,

Martin

CREATE PROCEDURE user_email()

LANGUAGE SQLSCRIPT AS

BEGIN

    DECLARE v_email   NVARCHAR(256) default '';

    DECLARE v_user    NVARCHAR(5000) default '';

    DECLARE v_stmt    NVARCHAR(500) default '';

    DECLARE CURSOR c_1  FOR

SELECT user_name, email FROM MYSCHEMA.USER_EMAIL;

    FOR cur_row AS c_1

DO

v_email := cur_row.email;

v_user := cur_row.user_name;

v_stmt := concat('ALTER USER ', :v_user);

v_stmt := concat(:v_stmt,' SET PARAMETER email address = ');

v_stmt := concat(:v_stmt, '''');

v_stmt := concat(:v_stmt, :v_email);

v_stmt := concat(:v_stmt, '''');

        EXEC v_stmt;

    END FOR;

END;

Former Member
0 Kudos

Hello

No need for separate concat statement, it can be done as one.


create procedure concat_demo()

language sqlscript as

begin

  declare v_email   NVARCHAR(256) default '';

    declare v_user    NVARCHAR(5000) default '';

  

    declare v_stmt    NVARCHAR(500) default '';

  v_email := 'michael_eaton@somewhere.com';

  v_user := 'michael_eaton';

  v_stmt := 'alter user ' || v_user || ' set parameter email address = ''' || :v_email || '''';

  select :v_stmt from dummy;

end;

The result being


alter user michael_eaton set parameter email address = 'michael_eaton@somewhere.com'

Michael

martin_chambers
Participant
0 Kudos

Thanks Michael,

have you missed a colon before v_user, i.e. should it read


     v_stmt := 'alter user ' || :v_user || ' set parameter email address = ''' || :v_email || '''';

?


Do you know whether I could write the whole thing as a .hdbprocedure without dynamic SQL?


Martin

michael_eaton3
Active Contributor
0 Kudos

Hello

The missing : is a typo by me, although HANA doesn't require them in all situations, so my example still worked.

I'm not 100% sure, but I don't think you'll be able to issue DDL directly from any kind of procedure, you need to use dynamic SQL as Lars said.

Michael

Answers (0)