Previous Topic

Next Topic

Book Contents

Book Index

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:

Copy to SQL Window

From the Run Procedure window, you can use Copy to SQL Window to create an SQL script to run the procedure. This is the recommended method when wishing to run a procedure from the SQL window.

Run Procedure Syntax

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,parmname=custname,value=Smith
--aqt parms,parmno=2,parmname=procreturn,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.

parmname is an optional parameter which you can use to identify the parameter. It is ignored by AQT.

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:

This doesn't work for a stored procedure since:

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:

Advanced Query Tool
https://www.querytool.com
© 2023 Cardett Associates Ltd. All rights reserved.