Skip to Content
Author's profile photo Rich Heilman

SQLScript Procedure Templates in SAP HANA

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”.


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


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.


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”.


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.


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


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.


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.


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.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Kamal Mehta
      Kamal Mehta

      Hi Rich,


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

      Author's profile photo Former Member
      Former Member

      Thank you Rich, extremely helpful stuff.

      Author's profile photo Former Member
      Former Member

      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 !