Running a Stored Procedure

The normal method of running a stored procedure is to use the Run Procedure window. However you can also run a stored procedure from the Run SQL window. This is useful in the following circumstances:

• you want to save the stored procedure execution as a saved query
• you wish to run a stored procedure in batch mode
• you want to export the results of a stored procedure to a file
• you are running a Stored Procedure contained inside an Oracle Package.

The ODBC syntax for running a stored procedure is:

{ call procedure_name () }

When you are running a stored procedure contained inside an Oracle Package, the syntax is:

{ call package_name.procedure_name () }

Procedure Parameters

If the stored procedure has input parameters only, you can run it with either of the following formats

{ call procedure_name ('Smith',23) }

or

{ call procedure_name (?,?) } 

(and enter the parameters when prompted)

or

--aqt parms,parmno=1,value=Smith

--aqt parms,parmno=2,value=23

{ call procedure_name (?,?) }

The latter format is the recommended format, as most database prefer having the procedure parameters passed as query parameters. Some databases have trouble processing the first of these formats.

OUT and INOUT Parameters

If the stored procedure has either OUT or INOUT parameters, you must run it using the following format:

--aqt parms,parmno=1,value=Smith

--aqt parms,parmno=2,ptype=out

{ call procedure_name (?,?) }

Notice that when specifying the parameters, you must code a ptype clause if your parameter is either an OUT or INOUT parameter. The procedure will only run successfully if the parameters are specifed with the correct type.

You can generate the correct SQL (and control statements) for running the stored procedure by clicking on the Copy to SQL Window button from the Run Procedure window.

Return Parameters

If you have either OUT or INOUT parameters, the status message at the bottom of the Run SQL window gives the value of these parameters after the procedure has executed. You can also see these with Window > Procedure Parameters (this menu item will only be visible if you have run a procedure with out/inout parameters).

Specifying the Data Type of Parameters

Some databases (for instance DB2 z/OS) also require you to specify the data type of the parameters. This is done by coding the DTYPE clause in the parms statement. Example:

--aqt parms,parmno=1,value=Smith,dtype=character(30)

--aqt parms,parmno=2,ptype=out,dtype="decimal(5,2)"

{ call procedure_name (?,?) }

The dtype value must be enclosed in double-quotes if it contains a comma (as in the above example).

Technical discussion

A technical explanation of ptype and dtype parameters is given below.

The way in which stored procedures are processed (by your database) is quite different to normal queries. For a normal query:

• AQT does a prepare of the SQL
• AQT then does a query parameters. The database returns the number of substitutional parameters which are in the SQL, and the data type of each parameter.
• AQT then "binds" the parameters to the query, and executes the query.

This doesn't work for a stored procedure since:

• the information returned by query parameters does not include the parameter type (IN, OUT, INOUT). However this must be specified correctly when you bind the parameter to the query.
• some databases (eg. DB2 z/OS) do not allow you to do a query parameters when your SQL runs a stored procedure.
• some databases (eg. Oracle) do not return accurate information about the query parameters.

Instead, for stored procedures, AQT needs to obtain information on the parameters by querying the procedure information in your database system tables. This is done when procedures are run from the Run Procedure window. However, this is not done(*) when the procedure is run from the SQL Window, or when the procedure is run in batch mode. In these cases, you need to supply this information by use of the parms statement, and the ptype and dtype clauses.

These clauses will be generated correctly if you use the Copy to SQL Window function of the Run Procedure window.

(*) Why doesn't AQT query the system tables when procedures are run from the SQL window? There are two reasons for this:

• this would add extra system overhead and complexity to the running of stored procedures
• the queries that get the information on stored procedures from the system tables can be unreliable for some databases (in particular Sybase). By coding ptype and dtype parameters, you have control of the values and can circumvent any bad information returned by the database.