Creating users within stored procedures

[Former Member]

Hi,

I was trying to create a stored procedure for creating
users and configure them, but I’m stopped hard by trying to create a stored procedure for
the trivial approach that looks like this

create

procedure "createUser" (out result integer) language sqlscript as

begin

create user NEWUSER password HelloWorld1;

end;

Could not execute 'create procedure "createUser" (out result integer) language sqlscript as begin create user NEWUSER ...'

SAP DBTech JDBC: [257] (at 88): sql syntax error: incorrect syntax near "user": line 2 col 14 (at pos 88)

The create user stmt runs fine directly from the SQL Editor. I have a similiar problem with creating a schema from a strored procedure.

Any ideas ?

Thanks, Philipp

[Former Member · Accepted solution]

Hi Philipp,

You can use the EXEC statement, replacing

create user NEWUSER password HelloWorld1;

to

EXEC 'create user NEWUSER password HelloWorld1';

You can also make this dynamic:

CREATE procedure "createUser" (IN NUSER NVARCHAR(20), out result integer) language sqlscript as

begin

EXEC 'CREATE USER ' || :nuser || ' password HelloWorld1';

end;

Thanks

[Former Member · Accepted solution]

Hi Anooj !

Thanks a lot, that worked.

Kind regards, Philipp