Creating Stored Procedures

Aginity Netezza Workbench allows managing stored procedures easily and transparently. 

The stored procedures in a database are available under the Stored Procedures node of the database in the object explorer tree. 

To create a stored procedure:

1. Do one of the following: 

a. Right-click the Stored Procedures node of the database in the object explorer tree that you want to create the stored procedure for and select Create Procedure in the context menu.

b. In the object explorer tree, select the node of the database that you want to create the stored procedure for and then click Object Procedure Create Procedure in the top menu.

2. In the General section of the Stored Procedure Properties form, enter the name of the procedure and its language and specify whether you like to execute the procedure as the owner.

Let's call the new procedure MY_NEW_PROCEDURE:

sp1.png


3. In the Arguments section, enter the variables for the procedure and set their data types, precision, and scale.

Let's specify one argument named "Result" here and select DATE as the data type for it:

sp2.png


Note that you can declare more variables later on if necessary, by editing the SQL query text.

4. In the Returns section of the form, select the data type, precision, and scale for the return data. You can also specify the reference table name here if necessary.

In our example, we will select DATE as the data type:

sp3.png


Click OK when done.

5. As a result, a new tab is created in the Query Editor window. The tab is named MY_NEW_PROCEDURE and contains the basic SQL query text that looks like the below:

sp4.png


Enter the query text at the cursor position — between BEGIN and END. 

Let's enter the following here: 

    Result := MIN(DATE) FROM TABLE;
    RETURN Result;


Now the code of the stored procedure looks like this:

sp5.png


When called, this procedure will return the minimum value from the DATE column of the TABLE1 table in the current database.

6. Press CTL+F5 (execute as a single batch) to execute the SQL statement and finish creating the stored procedure. The name of the new stored procedure will appear in the Stored Procedures node of the database in the object explorer tree.

Have more questions? Submit a request

1 Comments

  • 0
    Avatar
    Eli Weine

    If you are trying to compile a stored procedure in Workbench and you see an "unterminated BEGIN_PROC string"  error, make sure you execute the create stored procedure block using "Execute as a single batch" Ctl+F5

    OR

    Under Query -> Current Query Options change the query type to SP/Function

    Edited by Eli Weine
Article is closed for comments.
Powered by Zendesk