Using Stored Procedures in a DB2 Database

A stored procedure is an executable object stored in a database. Calling a stored procedure is similar to invoking an SQL command. Using stored procedures on the data source, instead of executing or preparing a statement in the client application, can provide several advantages, including: higher performance, reduced network overhead, and improved consistency and accuracy.

Stored procedures offer many advantages in data-driven applications. Database operations can be encapsulated in a single command, optimized for best performance, and enhanced with additional security. While a stored procedure can be called by simply passing the stored procedure name followed by parameter arguments as an SQL statement, using MsDb2Command.Parameters enables you to more explicitly define stored procedure parameters as well as to access output parameters and return values.

To call a stored procedure, set MsDb2Command.CommandType to StoredProcedure. You can then use Parameters to define parameters.

You can create a parameter using the appropriate constructor or by calling Command.Parameters.Add. Parameters.Add takes as input either constructor arguments or an existing Parameter object. When setting the value of a parameter to a null value, use DBNull.Value.

For parameters other than input parameters, you must set the MsDb2Parameter.Direction property to specify whether the parameter type is Input, Output, or InputOutput.

When using parameters with MsDb2Command, the names of the parameters added to the MsDb2ParameterCollection must match the names of the parameter markers in your stored procedure. The Managed Provider for DB2 treats parameters in the stored procedure as named parameters and searches for the matching parameter markers.

The Managed Provider for DB2 does not support named parameters for passing parameters to an SQL statement or a stored procedure called by CommandType.Text command. As such, you must use the question mark (?) placeholder, as shown in the following example:

SELECT * FROM Customers WHERE CustomerID = ?

As a result, the order in which MsDb2Parameters are added to the MsDb2ParametersCollection must directly correspond to the position of the question mark placeholder for the parameter.

You may need to return information from a database that is not in the form of a table or data stream. For example, you may want to return a single value such as the result of Count(*), Sum(Price), or Avg(Quantity). Command provides the capability to return single values using MsDb2Command.ExecuteScalar. ExecuteScalar returns the value of the first column of the first row of the resultset as a scalar value.

See Also

Using the Managed Provider for DB2

To download updated Host Integration Server 2004 Help from, go to

Copyright © 2004 Microsoft Corporation.
All rights reserved.