There are some general features and limitations concerning generated stored procedures that should be noted.
- Stored procedures are always generated in the login user database. This is necessary so that SQL Data Definition Language (DDL) statements can be included in the procedure.
- Stored procedures will include any post-processing that SQL requested in an included analysis.
- Some SQL constructs are not allowed in a stored procedure, including SQL to create a recursive view (an option available in the Variable Creation analysis).
- To execute a stored procedure, a CALL statement may be included in a Free Form SQL analysis or in another application. For example, the following executes a stored procedure called “test_procedure” where the user is “twm”, the results database is “twm_results” and the desired output table name is “test_output”:
CALL “twm”.”test_procedure” ('twm_results', 'test_output'); - If the SQL to create a stored procedure is ever placed in a Free Form SQL analysis, the option to Execute as a single statement must be used to execute it.
- In order to support the use of SQL to drop and create tables, generated stored procedures include an error handling control statement to allow a table-not-found condition. This error handler however may hide other errors such as improper access rights and incorrect positional assignment lists (on inserts). The user is cautioned to check warnings and messages on the RESULTS-data tab that may alert the user to these and other errors.