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.

** Aginity, Inc.’s Provision of Scripts and Similar Materials at Help Desk Center. For the convenience of Aginity Amp™ clients, we provide code snippets, scripts and similar materials at this Help Desk Center. Such materials are reference materials provided for illustration purposes only. These are intended to serve as an example for self-service clients and are generally geared to respond to common questions asked by similar clients. Such materials constitute Aginity’s intellectual property. Aginity Amp clients and their authorized users are permitted to use these materials in connection with their software license and/or subscription of Aginity Amp. Nothing herein shall limit Aginity’s right to use, develop, enhance, modify or market any of these materials as part of its business. These materials are not formally supported by Aginity or its affiliates. Usage of these materials does not guarantee any specific results, uptime, performance or error-free operation. Aginity disclaims all warranties of any kind, whether express, implied, statutory or otherwise, including any implied warranty of merchantability or fitness for a particular purpose.

Powered by Zendesk