Skip to Content

One of the new features in SAP HANA 1.0 SPS6(Rev 60) is the ability to create procedures based on a procedure template.  Procedure templates allow you to create procedures with a specific interface(input and output parameters) but can contain generic coding leveraging placeholders or template parameters.  Currently only a subset of these placeholders can be used.  For example, you can create template parameters for a schema value, a column field name, a table or view name, or a name of a procedure.  In order to create a procedure template from the HANA studio, choose “New” then “File”.

/wp-content/uploads/2013/07/1_238507.png

In the following dialog, enter the name of the procedure template and add the file extension .proceduretemplate.

/wp-content/uploads/2013/07/2_238508.png

The procedure template editor allows you to define the template parameters, as well as the template script. In this example, I am creating a template which simply gets the number of rows from a table.  The table name will be inserted from the template parameter called “table”.  You will notice that I reference this parameter in my code by using angle brackets(< >).  You can give any name to the parameter as long as  you reference it with the same exact name and wrapped in these brackets. Again, you can only use these parameters in certain situations, like when specifying a schema, column field name, table name, or  procedure name.

/wp-content/uploads/2013/07/3_238509.png

Now that you have a procedure template, you can create a procedure based on that template.  You can do this from the new procedure wizard which has been introduced in SPS6 as well.  From your project, choose “New”, then “Other”.  In the SAP HANA Development folder, you will see an artifact called SQLScript Procedure. Choose this and click “Next”.

/wp-content/uploads/2013/07/4_238510.png

Enter the name of the procedure.  There is no need to type the .procedure file extension here. The wizard will add it for you automatically when you navigate out of this field.  Click the “Advanced” button.  Here you can specify the name of the procedure template which you would like to use to create your procedure from.

/wp-content/uploads/2013/07/5_238511.png

The procedure editor will allow you to define the values for the procedure templates. In this example, I am simply specifying the products table.

/wp-content/uploads/2013/07/6_238513.png

The runtime object which is generated in the _SYS_BIC schema will have the source code from the template and the values for the template parameters inserted accordingly.   If you were to change the template at any point, all procedures created based on this template would be updated and activated automatically.

/wp-content/uploads/2013/07/7_238514.png

Of course we can call this procedure from the SQL Console and the result set, which is the count from the products table, is shown.

/wp-content/uploads/2013/07/8_238515.png

So this feature has been introduced to help developers become more efficient, and have less redundancy in their coding by using templates to create procedures with very similar structures in both the interface as well as the code itself.  Check out the video demonstration on the SAP HANA Academy.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Kamal Mehta

    Hi Rich,

    Thanks.

    I have gone through some of your blogs and its being easy to understand and grasp the same based out of your writing.

    (0) 
  2. Pawan Rawat

    Hello Rich,

    Thanks for sharing the information. I am new to HANA and want to know if i can call a user defined function in a sql query.

    My requiement is like that i need to create a function which takes cursor as argument and returns the result. i need to call this function through a sql query. In oracle this is done through Cursor Expression. can i do the same in HANA ? a simple example would be a great help !

    Thanks for spreading the knowledge !

    (0) 

Leave a Reply