Using Template Tags in SQL Statements

The template tags you can use in an SQL statement are the same tags you use in an output template. Besides allowing you to specify the values of individual input parameters, template tags also allow you to dynamically construct entire portions of the SQL statement at run time. (For a complete list of template tags, see the Dynamic Server Pages and Output Templates Developer’s Guide.)

When you use template tags in an SQL statement, you cannot test the statement with the Integration Server Administrator; (the Integration Server Administrator does not recognize template tags and will not prompt you for input when you execute the service). To test a service that uses template tags, you must open that service in Designer and add to its input parameters any variables that are referenced in a tag. You can then test the service with Designer and it will prompt you for each input variable you defined. For information about declaring input parameters for a service, see the webMethods Service Development Help.

Example 1

The following shows an SQL statement that selects all rows satisfying the criteria that will be specified in a variable named condition at run time:

select * from Names where %value condition%

At run time, the server will substitute the value of condition for the %value% tag. The following shows examples of what you might use as the value of condition:

name = 'Steve'

name like 'Jim%'

Example 2

To delete all rows from the Music table that meet a specified condition, specify the following SQL statement:

delete from Music where %value outdated%

The service expects input for an input variable named outdated that it uses to replace the entire token. Following is an example of what a user can specify for %value outdated%:

ReleaseDate < '1950'

Format = '8-track'