Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 11 at 05:24 PM

    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;
    Add comment
    10|10000 characters needed characters exceeded