SAP HANA feature not supported: parameter is not allowed without alias

[former_member437692]

Hi Team,

I am trying to create a stored procedure in SAP HANA to create business users with user name being input.

Following is the code snippet.

CREATE PROCEDURE PROC_CREATE_BUSINESS_USERS(IN USER_NAME VARCHAR(100))
LANGUAGE SQLSCRIPT AS


current_sql nvarchar(300);


BEGIN
current_sql = SELECT 'CREATE USER' || :USER_NAME || 'WITH IDENTITY ' || '''' || :USER_NAME || '''' || ' FOR SAML PROVIDER HANABOBJSAML VALID FROM NOW UNTIL FOREVER' FROM DUMMY;


EXEC(:current_sql);


END;

When I try to create this procedure, I get following error message.

Could not execute 'CREATE PROCEDURE PROC_CREATE_BUSINESS_USERS(IN USER_NAME NVARCHAR(100)) LANGUAGE SQLSCRIPT AS ...' SAP DBTech JDBC: [7]: feature not supported: parameter is not allowed without alias: USER_NAME: line 7 col 39 (at pos 173)

We are in SAP HANA SPS12 Rev 122

Thanks,

Venkat

[KonradZaleski · Accepted solution]

1. You need to add alias for the created string like: SELECT 'AA' || 'BB' AS "TEST" FROM DUMMY

2. You cannot use table variable for EXEC (you need to declare and use scalar variable)

Before running make sure that your CREATE USER statement is valid (for example you might miss space after CREATE USER string).

Try something like this:

DO
BEGIN
   DECLARE current_sql VARCHAR(500);

   SELECT 
     'CREATE USER ' || :USER_NAME || 'WITH IDENTITY ' || '''' || :USER_NAME || '''' || ' FOR SAML PROVIDER HANABOBJSAML VALID FROM NOW UNTIL FOREVER'  AS "SQL"
   INTO 
      current_sql
   FROM 
      DUMMY;

    EXEC(:current_sql);
END;

[former_member437692 · Accepted solution]

Hi Konard,

Thank you very much for quick reply.

I was able to successfully create following procedure.

CREATE PROCEDURE PROC_CREATE_BUSINESS_USERS(IN USER_NAME VARCHAR(100))
LANGUAGE SQLSCRIPT AS
BEGIN
   DECLARE current_sql VARCHAR(500);
   SELECT 
     'CREATE USER ' || :USER_NAME || 'WITH IDENTITY ' || '''' || :USER_NAME || '''' || ' FOR SAML PROVIDER HANABOBJSAML VALID FROM NOW UNTIL FOREVER'  AS "SQL"
   INTO 
      current_sql
   FROM 
      DUMMY;
    EXEC(:current_sql);
END;

If I call this procedure with following statement

CALL SYSTEM.PROC_CREATE_BUSINESS_USERS('NEW_USER')

I am getting error 'incorrect syntax near "IDENTITY": line 1 col 26 (at pos 26)'.

I tested 'CREATE USER NEW_USER WITH IDENTITY 'NEW_USER' FOR SAML PROVIDER HANABOBJSAML VALID FROM NOW UNTIL FOREVER;' statement and it works without any issues.

Seems like I am missing my logic with single quotes around user name. any help?

Thanks,

Venkat.

[KonradZaleski · Accepted solution]

Space is missing before WITH IDENTITY clause. It should be:

' WITH IDENTITY '

If this fix your issue please Accept my answer.

Thanks.

[former_member437692 · Accepted solution]

My bad. I missed that. Appreciate your respnse.

[lbreddemann · Accepted solution]

This is nearly a good solution and it definitively fixes the OP's question about the error message.

However, it can be improved. SELECT INTO ... FROM DUMMY is not required for string operations!

Also, the formatting is unfortunate as it makes it easy to overlook the missing space at 'WITH IDENTITY '.

Here's what I would write instead:

CREATE PROCEDURE PROC_CREATE_BUSINESS_USERS(IN USER_NAME NVARCHAR(256))
LANGUAGE SQLSCRIPT AS
BEGIN
DECLARE current_sql NVARCHAR(1000);

   current_sql := 'CREATE USER ' 
                || :USER_NAME 
    	        || ' WITH IDENTITY ' 
    	        || '''' || :USER_NAME || '''' 
    	        || ' FOR SAML PROVIDER HANABOBJSAML VALID FROM NOW UNTIL FOREVER';
   
   EXEC(:current_sql);
END;

[KonradZaleski · Accepted solution]

Good point lars.breddemann !