Skip to Content
avatar image
Former Member

DDL in Stored procedures

Hi All,

I would like to create a stored procedure to drop and create a synonym where the synonym will be passed an input to the stored procedure. Please let me know how this can be done.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Oct 21, 2016 at 02:18 AM

    Hi,

    The main point is to use exec to execute ddl command, use || to append command.

    The structure would like below, (I only list create synonym):

    -----------------------------------------------------------------------

    create procedure synonym_create (in synonym_name NVARCHAR(20), in table_name NVARCHAR(20))
    as
    begin
    exec 'create synonym ' || :synonym_name || ' FOR ' || :table_name;
    end;

    ---------------------------------------------------------------------------
    Regarding the create, drop synonym option, please do refer to links:

    https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/d7e172751910148bccb49de92d9859/content.htm

    https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/d5412b75191014bc7ec7e133ce5bf5/content.htm


    Best regards,
    James

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 21, 2016 at 03:42 PM

    As for the product roadmap, we are looking to allow the use of DDL statements natively within procedures in an upcoming release.

    Cheers,

    Rich Heilman

    Add comment
    10|10000 characters needed characters exceeded