Execute Command

Runs a SQL command (INSERT, UPDATE, DELETE…) on your SQL Server or MySQL database and returns the number of affected rows.

Execute Command runs a SQL statement that changes data on your own database, such as INSERT, UPDATE, DELETE or a stored procedure call, and gives you the number of rows it affected.

Query or command? A command changes data and returns the number of affected rows. A query reads data and returns rows; use Execute Query 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 Command (or type Execute Command in the search box).

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

Then click the Execute Command card to open the command editor.

The Execute Command configuration: Command, 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.
Command (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 command 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	Number	Yes	The number of rows the command affected.

Example

Command:

UPDATE Orders SET Status = @status WHERE CustomerId = @customerId

Parameters status → Shipped, customerId → 42. If customer 42 has two orders, Output is 2.

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

Use SQL parameters

Instead of pasting variables into the SQL text, write parameters (@firstName, @email) and map them in the parameters table:

INSERT INTO Clients (FirstName, Email) VALUES (@firstName, @email)

Parameter	Value
`firstName`	the process variable that holds the first name
`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 command (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

Output is the count the database reports. On SQL Server, a command that starts with SET NOCOUNT ON reports -1, and a statement that changes nothing (for example CREATE TABLE) can also report -1 or 0.
To get back data from the same statement (for example the new id), use Execute Query with INSERT … OUTPUT INSERTED.Id (SQL Server) or a SELECT after the insert.
If the database reports an error, the step fails. If the credential is missing or invalid, it fails with "Invalid credentials!".
Each run of the action is one call to the database. To insert many rows, prefer one command with several rows over one action per row in a For Each.

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 command 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 Query, GUID Converter