You can create an operation for a stored procedure manually or by browsing for the procedures in a database. Browsing is easier than creating it manually because information about the parameters, return value, and result sets can usually be retrieved from the database. Before you can browse a database to select a stored procedure for a database operation, you must configure an endpoint connection to provide access to the database.
When you add a stored procedure operation to a service, you are setting up that service to invoke a database stored procedure, which is a pre-defined statement or set of statements in a database. The operation has parameters that match the stored procedure parameters, a return value if the stored procedure has one, and results sets if the stored procedure returns them. The information you specify must match the actual inputs and outputs of the stored procedure.
Individual operations can be configured to enable or disable transaction autocommit. The autocommit setting controls whether the operation causes a transaction to be automatically committed. If the checkbox is selected, all database calls are completed in a separate transaction so that a business process rollback does not result in a database transaction rollback. If the checkbox is not selected, a business process rollback results in rollback of the database transaction. Set the checkbook on Operation Type to true for backward compatibility.
You must specify the following when you create an operation for a stored procedure:
Parameters for the stored procedure. The set of parameters for the operation must match the parameters for the stored procedure.
The return value for the stored procedure. If the stored procedure has a return value, you must specify this when you create the operation.
How to map the inputs and outputs of the stored procedure to an XML schema.
Result sets returned by the stored procedure. You must define any result sets that are returned by the stored procedure when you create the operation.
The inputs and outputs of a stored procedure operation appear as an XML schema in the database service that contains the operation. The parameters, return value, and result sets that you specify for the operation appear as parts of the schema. The schema is used when you create a service interaction for the operation and add it to a business process.
Unlike the manual work required for adding a SQL statement operation, the wizard provides a list of available procedures. For some databases, the wizard provides parameters, result sets, and columns for the one result set. For procedures that do not automatically populate the Result Set, use the Discover result sets... button on the Result Set page of the wizard.
Stored Procedure Parameters
|
Parameter |
Description |
|
Name |
User defined. |
|
Number |
The order of the parameters. You cannot edit the number; the order must be the same as the order in the query. |
|
DB Name |
Must be the same as it appears in the SQL query. |
|
Mode |
Designates whether the parameter is an in, out, or in/out. If the value appears as unknown, you must set it to match the mode of the parameter in the stored procedure. The stored procedure text may be helpful in determining the mode for parameters marked unknown. |
|
Nullable |
Designates whether the parameter can be null. |
|
Type |
Designates the database data type, such as char, varchar, or binary. |
|
Scale |
The scale (places to the right of the decimal point) for decimal and numeric parameters. |
Using the New Service Wizard for a database service, find a stored procedure one of two ways:
Browse a database to select the procedure, in which case the wizard automatically fills in any information about the procedure that it can. If the wizard is not able to determine the stored procedures result sets when you select the procedure, you can use the Discover Result Sets option to execute the stored procedure and return all available result sets.
Enter the information about the procedure manually. This option is more difficult because the wizard cannot assist you. It is preferable only for creating an operation for a stored procedure that does not exist or if you do not have access to the database that contains the stored procedure. Browsing the database is the preferred way to create stored procedure operations.