Code

To add the SQL code for the stored procedure, select the node in the left-hand pane that corresponds to the database type used in your runtime environment, namely :

• Code MsSQL - Microsoft SQL database
• Code Oracle - Oracle SQL database
• Code Db2 - IBM DB2 database

The SQL code is entered in the right-hand pane. To use parameter names in the code, prefix them with the "@" sign. Refer to the documentation for your database management system to learn which syntax can be used.

Note:

1. The AMT Developer Studio does not perform any syntax checks on this code, therefore you must test it manually. If stored procedures are generated with code that is not valid for the database system that is used at runtime, this will lead to errors during the database reorganization.
2. AMT does not support multiple result sets in stored procedures. Therefore, if a stored procedure contains 2 SELECT queries, only the first can be used to read the result set. For the second one, you need to use Output parameters to get information, but you cannot return a result set cursor.
3. Oracle and Db2 stored procedure code should always contain the keywords BEGIN and END with the executable section of the code between those two keywords.
4. Oracle does not support the SELECT statement in stored procedures in a generic way. This means that when a SELECT statement is used in a stored procedure for an Oracle database, it will not return a result set. Therefore, you will then have to use SELECT in combination with INTO, in order to put the results into a second table.