Editing SQLScript - Tools for Writing Procedures

Tools for Writing Procedures

8.1 Editing SQLScript

The SAP HANA SQLScript editor allows you to create, edit and activate procedures.

Before you begin working in the SAP HANA SQLScript editor, open the SAP HANA Development perspective and do the following:

● Create a development workspace. For more information, see Creating a Repository Workspace [page 43].

● Checkout a package. For more information, see Working with the Repository [page 30].

Note

After checking out a package that contains active procedures, you can modify and debug the procedures.

● Create and share a project. For more information, see Using SAP HANA Projects [page 42].

Note

You can also share your project after you create your procedure.

To write and edit a procedure in the SAP HANA SQLScript editor, perform the following steps:

1. After you have created your workspace and your project, go to the Project Explorer view in the SAP HANA Development perspective, right-click on the file name, select New, and select File. The New File wizard will appear.

2. Enter or select the parent folder and enter the file name using the following naming convention

<filename>.procedure. Choose Finish. The icon shows that your procedure is created locally. Choose Save.

Your procedure will open containing the default Create Procedure template. In the Properties view, you will see the properties of your procedure, such as Access Mode, Name and Language. You can also change the default schema that this procedure is using.

Note

You can also create a folder first and add a file. Right-click on the project name, select New, and select Folder. The New Folder wizard will appear. Enter or select the project, enter the folder name, and choose Finish.

3. To share your project, right-click on the project name, select Team, and select Share Project. The Share Project wizard will appear. Choose Finish. The icon shows that your procedure is not committed and not activated.

4. You can begin writing your code inside your new procedure and save it locally. The syntax is checked simultaneously and is highlighted. Auto-completion of the syntax appears as you type; press Ctrl + Spacebar to get a list of relevant SQLScript statements.

Note

You can only write one stored procedure per file. The file name and the procedure name must be the same.

5. To commit your new procedure or make changes to an existing one, first save it, right-click on the procedure, select Team, and select Commit. Your procedure is now synchronized to the repository as a design-time object and the icon shows that your procedure is committed.

Caution

The design-time presentation of the procedure is currently in XML format that you must not edit.

6. When you have finished writing your procedure and you are ready to activate it, right-click on the procedure, select Team, and select Activate. Your procedure is created in the catalog as a runtime object and the icon shows that your procedure is activated . This will allow you and other users to call the procedure and debug it.

If an error is detected during activation, an error message will appear in the Problems view.

Note

You can also activate your procedure at the project and folder level.

8.1.1 Defining Local Table Types in Procedures

Before you define local table types in the SAP HANA SQLScript editor, you must create or open a procedure. For more information, see Editing SQLScript.

To define local table types in a procedure in the SAP HANA SQLScript editor, perform the following steps:

1. Choose the Local Table Types tab.

2. Define your local table type structure using a standard SQL create statement. The local table type is specified using a list of attribute names and primitive data types. For example:

CREATE TYPE <type_name> AS TABLE (<column_definition>[{,<column_definition>}...])

Note

You can create multiple CREATE TYPE statements.

Caution

You can only use this local table type in the procedure you defined them in.

3. Use the local table table type as input and output parameters of the procedure, for example:

CREATE PROCEDURE <procedure_name> ( IN|OUT|INOUT <param_name> <type_name>, ... )

Caution

You can only use this tab to define local table types and not for other SQL statements.

4. Click the Save button. Commit and activate your procedure to create a local table types in the catalog. For more information about committing and activating a procedure, see Editing SQLScript.

Note

The local table types are bound to the procedure artifact, so if the procedure is committed, activated, or deleted, then the same applies to the local table type. For example, if you delete a procedure the local table type will be automatically deleted (similar to a drop statement) from the catalog.