You can pass multiple parameters into an SQL query in an ST program. This is useful when you want to apply additional criteria that has to be met by the values in the RESULTSET (this works similarly to a filter) and is also useful when you are using DML statements to insert multiple values for one or more rows in a table (see Inserting, Updating, and Deleting Values by Using SQL in an ST Program).
To enter multiple parameters in an SQL query within an ST program, you need to use the ? character to represent the value of each parameter and the WITH_PARAMS keyword to define the parameters.
When entering multiple parameters in an SQL query in an ST program, you need to use the ? character to represent each parameter value in the query. The WITH_PARAMS keyword has to be used in the RESULTSET and each parameter listed after the WITH_PARAMS keyword has to correspond to the name of one of the ST program's VAR inputs. You should separate the parameters after the WITH_PARAMS keyword with commas.
The order of the parameters that you define after the WITH_PARAMS keyword corresponds to the order of the ? characters defined in the SQL query. You can repeat the parameters so that the same parameter can be used multiple times in the SQL.
Example:
In this example of an ST Program, the parameters for the SQL query are defined as VAR_INPUTs with the names objName and objFullScale. In the SQL query, the ? character is used to represent the parameters. The DATABASE_OBJECT is used to define the database table (in this case, CPointAlg) and the type of value for the properties defined in the SQL query (Name and FullScale).
- TYPE
- Point: DATABASE_OBJECT (CPointAlg)
- Name:STRING;
- FullScale: LREAL;
- END_DATABASE_OBJECT;
- END_TYPE
- PROGRAM NameAndFullScale
- VAR_INPUT
- objName : STRING;
- objFullScale: LREAL;
- END_VAR
- VAR
- objList AT %S(SELECT Id, Name, FullScale FROM CPointAlg WHERE Name LIKE? || '%' AND FullScale > ?) : RESULTSET OF Point WITH_PARAMS objName, objFullScale;
- END_VAR
- ;
- END_PROGRAM
So, the SQL query in the ST Program will return the Id, Name, and FullScale values from the CPointAlg database table. It will only return the values for those items that have a Name value that is like the value of the first parameter followed by any amount of characters and has a FullScale value that is greater than the value of the second parameter. The first parameter is the objName parameter as this is the first parameter listed after the WITH_PARAMS keyword. The objFullScale parameter is the second parameter as it is listed second after the WITH_PARAMS keyword. So in the SQL query, the first ? character corresponds to the objName parameter and the second ? character corresponds to the objFullScale parameter. If the SQL query was to contain an additional ? character, an additional parameter would be required in the list of parameters after the WITH_PARAMS keyword. The additional parameter in the list could be a new parameter or could be one of the existing parameters used again, for example, it could be another use of the objFullScale parameter.