SQL Query - WF

The SQL Query Settings dialog is divided into several sections.

Header

Query Title: The name of the SQL Query list results that is generated when the SQL Query activity is executed as part of Workflow instance processing. The list title is used to locate the list within the Workflow instance internal data by the user and other Workflow activities.
Import Data: Workflow Instance data available during Workflow activity execution can be defined in the SQL statement. Data can be imported into the SQL statement from Web Form data or Workflow Data Variables. When Web Form or Data Variable definitions exist for the Workflow model, these variables will be used to populate the Import Data drop-down list. When the user wants to insert a value at run time, the cursor is first placed in the SQL statement where the variable placeholder will be inserted into the statement. The desired option from drop-down list is selected, and the Import button is selected. The result is a place holder for the variable being inserted where the cursor is currently sitting.
Display Rows: The ability exists with the SQL Query Workflow Activity to test the SQL statement prior to saving it in the Workflow Model rules. When executing the SQL Query Workflow Activity from the Settings dialog box, only 100 rows are retrieved from the DB. The user can set the number of displayed rows to be any value between 1 and 100 for testing purposes. Any number entered outside that range is blocked and replaced with 100. Note that the Workflow engine execution of the activity is without a row limit. All valid rows are returned that meet the SQL statement criteria.

SQL Statement

The SQL statement is entered into the SQL editor section. It is part of a split container that includes the SQL statement and the resulting message.

The SQL editor is used to define the SQL statement that will run every time the SQL Query Activity is executed as part of a Workflow model. The statement can have embedded variables that are defined in the Workflow model definition.

Embedded variables are used as placeholders for values that are not known until run time. The placeholder is replaced with the value in the variable for the Workflow instance record being processed at run time.

When verifying a SQL statement in the SQL Query dialog box, it is necessary to include real values in the SQL statement and not data or Web form variable placeholders. This is because the variables point to real data that has not yet been created. Placeholders should be inserted into the SQL statement after testing the SQL statement in the dialog box and prior to saving.

The E-Mail (Notify) activity inserts the SQL Data into the Custom Subject by creating a column name email_subject. Refer to the Email Notify page for more information.

Sample SQL that will create a Birthday List for the current month:

select id, name, e_mail, convert(datetime, bdt) as bdt, ('Birthday List for ' + DATENAME(month,getdate())) as email_subject 
from hr_empmstr where month(bdt) = month(getdate()) and stat='A' and hr_status='A'

Messages

The Message tab is a text area that displays the results of the SQL statement when the Test SQL button is selected.

If the user defines an invalid SQL statement, selecting the Test SQL button will result in a database-specific error message, displayed in the message window.

Results

The Results tab is a grid that contains the results of the SQL statement. It is only displayed after a successful SQL statement execution. Each row is a record returned with columns defined for each property returned in the SQL statement.

Each cell returned in the results tab can be highlighted and copied for troubleshooting and verification purposes.

Footer

Test SQL: Allows users during configuration to test the SQL statement that will be executed within a Workflow model. Validation is performed when the button is clicked. An error is displayed on the Message tab when the SQL statement is incorrect. A successful execution results in the returned information being displayed in a Results tab in the Messages section.
Cancel SQL: If the tests of a SQL statement are taking too long to execute, the user can select Cancel at any time and cancel the SQL statement. This button is only enabled when the Test SQL button event is being handled. Upon return from the SQL testing, the Cancel button is disabled.
Cancel: The user can cancel whatever changes were made to an SQL Query activity setting while in the dialog box by clicking the Cancel button. Any changes were made to the SQL Query activity setting will be lost.
Save: The user can save the results when they are done creating or modifying the SQL statement. The results are saved into the Workflow model rules internal data and executed the next time a Workflow instance executes the Workflow activity.