Use the Database Operation Wizard to create a SQL statement operation.
When you create an operation for a SQL statement, specify:
The SQL statement that will be executed. The SQL statement can contain parameters that are passed as inputs to the operation at runtime.
Definitions for any parameters you specify in the SQL statement.
How to map the inputs and outputs for the operation to an XML schema.
Result sets returned by the SQL statement. The result sets you specify for the operation must match the result sets returned by the SQL statement. You can execute the SQL statement to define the result sets automatically or define them manually.
The inputs and outputs of the operation appear as an XML schema in the database service that contains the operation. The parameters and result sets that you specify for the operation all appear as parts of this schema. The schema is used when you create a service interaction for the operation and add it to a business process.
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 checkbox on Operation Type to true for backward compatibility.
You may find use of a SQL builder tool helpful when defining a SQL statement operation. Most databases include such a tool. If so, use it to build and test your SQL before creating an operation.