New Stored Procedure Entry

With the ODBC Stored Procedure Entry wizard, you can generate an entry based on a ODBC Stored Procedure. The input parameters of the stored procedure become the formal parameters of the ITP entry and the output parameters or columns of the stored procedure become the fields of the ITP entry. Stored procedures that use input/output parameters cannot be used with ITP. The stored procedures may return multiple sets of output parameters, as long as the sets are equally formatted. In this case you should define the entry as a FORALL entry.

You will have to:

• Select the ODBC Data Source
  The wizard will present a list of all the ODBC data sources it finds on the system. Here you can select the ODBC data source for which you want to define your entry. After selecting the data source and selecting Next you may be requested to log on to the database.
• Restrict Procedure retrieval
  On this page of the wizard, you can enter a catalog, schema and/or procedure if you want to limit the set of stored procedures to choose from. If you don't know what to enter please select Next to continue.
• Select the stored procedure
  In this list you see all the stored procedures in your database. optionally limited by the options entered in the previous wizard page. Select the stored procedure that you want the entry definition to be generated for.
• View details
  This list will usually show the parameters of the stored procedure as well as the output parameters/columns. Sometimes the stored procedures return value (indicating success or failure) will also be listed. Depending on the database management system and the ODBC driver it may be the case that the output parameters/columns are not listed. This is for example the case with Microsoft SQL Server. Selecting Next will then show an ODBC SQL statement to perform a kind of trial-run of the stored procedure to retrieve the definition of the output parameters/columns.Otherwise selecting Next will proceed to the wizard page for the entry details.
• Execute the stored procedure
  In this page the ODBC SQL call for running the stored procedure will be displayed. Replace the parameters in this call for a trial run of the stored procedure. The parameters are usually denoted as @parm. "Replace all with" lets you replace all parameters in the call in one go. Replacing the parameters with two quotes usually executes the procedure. Selecting Next will execute the stored procedure to retrieve the output definitions. When supported by the DBMS and the ODBC driver, transaction support will be used to rollback the execution of the stored procedure. This may be needed to prevent database updates.
  Normally, replacing the parameters with quotes will execute the stored procedure.
• View details
  The parameters and the output definitions as retrieved by executing the stored procedure are listed. selecting Next will proceed to the wizard page for the entry details.
• Entry attributes
  Specify the name of the Entry. By default, The wizard will give the name of the selected Stored Procedure, converted to ITP conventions.You must choose whether the Entry will be singular or plural (WITH or FORALL).
• Completing the New ODBC Stored Procedure Entry wizard
  Select Next to generate the code for this entry. The generated Entry definition will be inserted at the location of the cursor in the active DID document.