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.
Additional Details
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.