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.