Workflow Action - Utility - SQL Run

Action:

SQL Run

Category:

Utility

Version:

v1.0

Content:

The Run SQL action is one of the most powerful actions in Rock workflows. It allows you to execute any SQL query you provide and store the results into an attribute on the workflow. This action is great for looking up data in the database and using it to make decisions in the workflow.

Note that the SQL you provide can contain Lava merge fields to help pass in attribute values for your SQL. Consider this example.

Say you wanted to get the gender of the person stored in the attribute Requester. You could use the SQL below to achieve this.

DECLARE @PersonAliasGuid uniqueidentifier = '{{ Workflow | Attribute:'Requestor','RawValue' }}'
SELECT [Gender] 
FROM [Person] p
INNER JOIN [PersonAlias] pa ON pa.PersonId = p.Id
WHERE pa.[Guid] = @PersonAliasGuid
--note gender is stored in the database as a number where 0 = Unknown, 1 = Male and 2=Female 

When the action places the result of the SQL into the configured workflow attribute, the returned result must match the data type of the attribute. For instance, your SQL can not return text if the attribute configured is expecting a number. A few common attribute types that you will use often are listed below, each with what they are expecting as input.

• Boolean: True/False
• Person: Guid of a person alias
• Group: Guid of a group

Note: If you update something directly via SQL, the cache manager won't know about it so you'll need to take care of flushing it from cache yourself.