SQL Expressions and SQL Scripts

SQL expressions and SQL scripts both help calculate values. However there are some differences:

• SQL expression indicates a single SQL SELECT statement

• SQL script is a set of SQL statements

NOTE: SQL scripts contain Transact-SQL statements. Creating SQL scripts requires a good knowledge of Transact-SQL. For Microsoft SQL Server 2017, see the Transact-SQL Reference at https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-2017.
If you are using a different version of Microsoft SQL Server, see the relevant Microsoft resources.

In order to use a SQL expression or a SQL script as calculated values for fields or parameters in Alloy Navigator workflow, you will need to follow these steps:

1. Click the Build Expression button next to the Value field in the Update Field, Form Field, or Assign Parameter dialog box. The Build Expression dialog box opens. For illustration, see the figure in Using the Expression Builder.

2. Under Expression Type, make sure you choose either the SQL expression or SQL script option.

   NOTE: When you open the Build Expression dialog box for a non-text field or parameter, these are the only available options. For text fields and parameters, the Build Expression dialog box also offers the Text option.

   1. To specify a SQL expression:

      1. Under Expression Type, click SQL expression.

      2. In the text field below, type a single SQL SELECT statement returning a single value that matches the requirements matches the parameter type.

         NOTE: If all you need to do is evaluate a simple expression without querying the database, you may omit the SQL SELECT syntax and specify the expression as is. For example, you can actually specify 1 + 2, and Alloy Navigator will sum these numbers and produce 3 as the result.

   2. To specify a SQL script:

      1. Under Expression Type, click SQL script.

      2. In the text field below, type your SQL script commands. Make sure that there is at least one SQL SELECT statement. At runtime, Alloy Navigator will run the script and return the value of the last SELECT statement. If the statement produces multiple rows or multiple columns, only the very first value will be returned. The data it returns must match the parameter type.

3. Click OK. Alloy Navigator will analyze your input. If your SQL expression is invalid, an error message appears.

The following two examples illustrate the usage SQL expressions and SQL scripts:

• SQL Expressions and SQL Scripts as Assigned Values in Update Field Operations

• SQL Expressions and SQL Scripts in E-mail Notification Parameters

SQL expressions and scripts can also be used for calculating field values and Function parameters.