For additional functionality, you can specify one or more input parameters for a SQL query operation. This allows you to specify values in the query at runtime. You must provide the following information for each parameter:
Name, which is an alias for the parameter name. The value for Name defaults to the same value as the value for the parameter name. XML uses the value for Name to reference the parameter. For example, if the value for Name is MyId, then the XML that is used for the parameter must look like: <MyId>0001<MyId>. For SQL statements, changing the value for Name does not provide any benefit.
Mode, which you cannot change.
Nullable, which is whether the parameter value can be null.
Type, which is the data type for the parameter.
Scale, which applies to decimal numeric columns
DBName, which is the name of the parameter. Its value is provided by the SQL statement when a parameter is defined. The value for DBName cannot be changed.
SQL fragment, which is false if the parameter contains a literal value or true if the parameter contains a fragment of SQL as part of the statement.
The SQL query must define the appearance of the result set. Note the column number, which is the order it appears in the result set.
The order of the query must exactly match the order in the database.
Use the Discover Result Sets button to automatically populate result sets.
The relationship between Name and DBName is demonstrated given the following statement: select * from myTable where UNIT_ID>:UnitID UnitID is defined to be a parameter. When the SQL Statement Parameters property dialog appears, DBName is set to UnitID because that is the defined name of the parameter. Name is set to UnitID because the default value is also the defined name of the parameter. You can change the value of Name, but that provides no benefit.
The SQL statement you specify for an operation can contain parameters. Stored procedure in parameters become inputs in the schema, stored procedures out parameters become outputs in the schema, and stored procedure in/out parameters become inputs and outputs in the schema. for the operation at runtime. Parameters are specified in the SQL with as :parameter. For example, the following query selects customers from a customers table:
select * from customer where state = :state
The :state is a parameter that will be specified at runtime. Different states can be specified at runtime to cause the operation to retrieve customers in different states.