Execute Query

Runs a SQL query on your SQL Server or MySQL database and returns the rows as a list.

Execute Query runs a SELECT (or any statement that returns rows) on your own database and gives you the rows as a list of objects. Use it to look up customers, orders or reference data during a process.

Query or command? A query reads data and returns rows. A command changes data (INSERT, UPDATE, DELETE) and returns the number of rows it affected; use Execute Command for that.

Before you start

Create a database credential for your server first. See SQL credential. The credential can point to Microsoft SQL Server or MySQL.

Add the action

In the Process Designer toolbox, open Database › SQL and pick Execute Query (or type Execute Query in the search box).

The Execute Query action properties: Select Database Server and the Execute Query card

Then click the Execute Query card to open the query editor.

The Execute Query configuration: Query, Time Out, the parameters table and Output

Inputs and outputs

Field	Direction	Type	Required	What to put / what you get
Select Database Server	Input	Credential	Yes	The database credential to use.
Query (in the card)	Input	Text (SQL)	Yes	The SQL to run. Insert process variables with Ctrl+I (Windows) or Cmd+I (Mac), or use parameters (below).
Time Out (in the card)	Input	Number	Yes	How many seconds the query may run. Values from 1 to 59 are raised to 60. Empty means 1800 seconds (30 minutes).
Parameters config tab (in the card)	Input	Pairs of name and value	No	SQL parameters, one row each: the parameter name on one side, a fixed value or a process variable on the other.
Output (in the card)	Output	List of Object (JSON)	Yes	One object per row. The keys are the column names exactly as the query returns them.

Example

Query:

SELECT Id, FirstName, Email FROM Clients WHERE Country = @country

Parameter country → RO → Output:

[
  { "Id": 1, "FirstName": "Ana", "Email": "ana@example.com" },
  { "Id": 7, "FirstName": "Mihai", "Email": "mihai@example.com" }
]

(This example shows the shape of the result; it was not run against a real database.)

Use SQL parameters

Instead of pasting a variable into the SQL text, write a parameter (@email) and map it in the parameters table:

SELECT Id, FirstName, Email FROM Clients WHERE Email = @email

Parameter	Value
`email`	the process variable that holds the e-mail address

Parameters are safer and simpler than inserted variables:

a value with a quote (O'Brien) does not break the SQL;
the value cannot change the meaning of the query (no SQL injection);
an empty (null) value is sent as SQL NULL.

Each parameter name may appear only once in the table, otherwise the step fails with "Duplicate parameter: …".

Good to know

The query must return at most one result set. A batch with two SELECTs fails with "Expected maximum one table but received 2 tables!". A statement that returns no result set gives an empty list.
Column names become the keys of each object, so give computed columns an alias (SELECT COUNT(*) AS Total …).
If the database reports an error, the step fails. If the credential is missing or invalid, it fails with "Invalid credentials!".
Large results are loaded into memory at once. Filter and page in SQL (TOP, LIMIT, OFFSET … FETCH) rather than reading whole tables.

Comparing a Guid column

Write a Guid in the form the column holds. A column that stores Guids as 16 raw bytes (for example binary(16)) does not match the text 3f1a7c52-0000-4000-8000-0000000000aa: WHERE Id = '3f1a7c52-…' finds no rows, and shows no error. Convert the Guid with the GUID Converter action and put its Hex output in the query without quotes, for example WHERE Id = 0x527C1A3F0000004080000000000000AA. Use Hex (big-endian) instead if your database saved the bytes in the order of the text form. A SQL Server uniqueidentifier column accepts the standard text in quotes. See Guids in other databases.

Execute Command, GUID Converter, For Each

Execute Query

On this page