Run SQL Query

Audience: Low-code Engineers

Skill Prerequisites: Actions, Tokens, SQL

Runs a SQL script against the application database or another database. It can save values from the first row of the result in tokens, so later actions can use them.

Use it to read a single record, insert or update data, or call a stored procedure. By default the whole script runs in a transaction, so if anything fails, none of its changes are kept.

note

This action requires the Data Integration feature (Standard) to be licensed. If it isn't, the action fails with a message that the feature is unlicensed.

Typical Use Cases

• Load one record, such as a customer or an order, into tokens to prefill a form or build an email
• Insert a row and get back the new ID, for example with SELECT SCOPE_IDENTITY()
• Update or delete rows when a form is submitted or a workflow runs
• Call a stored procedure
• Read a count, a total or another single value, for example to use in a condition
• Read or write data in another database, using a Database connector or a connection string

Don't use it to

• Load many rows to loop over. This action only reads the first row. Use Create List from SQL instead.
• Insert a whole list of rows at once. Use Import List into Database instead.
• Create, read or update Plant an App entities when an entity action fits. Use Create Entity, Read Entity or Update Entity instead.
• Change DNN or Plant an App system tables directly. It can bypass caching and business rules. Use the dedicated actions, such as the user and role actions, instead.

Related Actions

Action Name	Description
Create List from SQL	Runs a query and saves every row as a list.
Load Users from SQL	Runs a query that returns user IDs and loads those users.
Import List into Database	Inserts or merges the items of a list into a database table.
Create Excel from SQL query	Runs a query and writes the result to an Excel file.
Execute Actions	Groups actions, with its own On Error actions.

Input Parameter Reference

Parameter	Description	Supports Tokens	Default	Required
Database	A Database connector to run the query against. If it's empty, the application database is used. See Connection.	No	empty	No
Override Connection String	A connection string, or the name of a connection string from web.config. When it's filled in, it's used instead of Database. See Connection.	Yes	empty string	No
Query Timeout	How many seconds the query can run before it times out. Empty or 0 means 600 seconds (10 minutes). Values from 1 to 9 are raised to 10. Negative or non-numeric values make the action fail.	Yes	600	No
Use Transactions	Runs the whole script in one transaction. If anything fails, all changes are rolled back. Turn it off for commands that can't run in a transaction, such as SHRINK DATABASE. See Transactions.	No	true	No
SQL Query	The SQL to run. Tokens are replaced with SQL-safe text, but you add the quotes. See Tokens in the query.	Yes	empty string	Yes
Bind Tokens	Parameters for the query. Each row has a Parameter Name, for example CustomerId, and a Parameter Value, for example [CustomerId]. Use @CustomerId in the query. This is the safest way to pass values.	Yes (values)	empty	No
On Error	Actions to run if the query fails. After they run, execution continues with the next action. They can use the [Exception], [ExceptionType], [ExceptionMessage] and [ExceptionStack] tokens.	No	empty	No
Show Errors	When on, and there are no On Error actions, the original SQL error is raised instead of a generic one. Useful when your SQL raises readable messages with RAISERROR or THROW.	No	false	No

Output Parameters Reference

Parameter	Description	Supports Tokens	Default	Required
Store Result	A token name for the result, for example Customer. [Customer] gets the first column of the first row. Every column is also saved as [Customer:ColumnName], for example [Customer:Email].	No	empty string	No
Extract Columns	Saves specific columns in tokens with names you choose. Each row has a Column Name from the result, for example Email, and a Store As token name, for example CustomerEmail. Every listed column must exist in the result, or the action fails.	No	empty	No

Date columns get three extra tokens, for example [Customer:CreatedOn:ISO] (ISO 8601 date and time), [Customer:CreatedOn:ISODate] (yyyy-MM-dd) and [Customer:CreatedOn:ISOAsUtc] (ISO 8601, treated as UTC). datetimeoffset columns get only :ISO and :ISODate. The same extra tokens are created for columns in Extract Columns.

Connection

The action picks the database in this order:

1. Override Connection String, if it's filled in
2. The connection string of the Database connector, if one is selected
3. The application database

Override Connection String can also be the name of a connection string in web.config. A named connection string uses its own provider, for example ODBC. A connection string entered directly, or taken from a connector, is opened as a SQL Server connection. To use another database engine, add a named connection string with the right provider to web.config and enter its name.

{databaseOwner} and {objectQualifier} in the query are replaced with the values of the application database.

Tokens in the query

Tokens in SQL Query are replaced before the query runs. Single quotes in token values are doubled, so a value can't end a string early. The action doesn't add quotes, so put text values in quotes yourself:

SELECT * FROM Customers WHERE Email = '[Email]'

Tokens that don't exist are left as they are, because square brackets are also SQL syntax, for example [dbo].[Customers]. So a misspelled token ends up in the SQL as [TokenName].

Bind Tokens are safer. The values are sent as real SQL parameters, not pasted into the SQL. Values are sent as text, and dates in token values are expanded in ISO format.

SELECT * FROM Customers WHERE Email = @Email

Files

With SQL Server, @File<path> in the query, or as a bind value, sends the contents of a file on the server as binary data, for example to save it in a varbinary(max) column. Use the full path to the file.

Transactions

When Use Transactions is on (the default):

• The whole script runs as one unit, with the Read Committed isolation level.
• If any statement fails, every change made by the script is rolled back.
• If saving the output fails, for example because a column in Extract Columns isn't in the result, the changes are also rolled back.

When it's off:

• Each statement is saved as it runs. If a later statement fails, earlier changes stay.
• Commands that aren't allowed in a transaction, such as SHRINK DATABASE, can run.

Errors

When the query fails, the Store Result and Extract Columns tokens are set to empty. Then:

• If there are On Error actions, they run and execution continues with the next action.
• Otherwise, if Show Errors is on, the original SQL error is raised.
• Otherwise, a generic error is raised. Low-code engineers and administrators see the SQL and the error. Other users see: There was an error with the data. Please contact the administrator or check logs for more info.

Considerations

• Only the first row is read, from the first result set. If there are no rows, the output tokens are empty. Use Create List from SQL for more rows.
• Output tokens start empty. They're cleared before the query runs, so old values don't carry over from earlier actions.
• Queries without a result, such as an UPDATE, are fine. Leave Store Result and Extract Columns empty. Extract Columns fails when the query doesn't return the columns it lists.
• Put text tokens in quotes, or better, use Bind Tokens. In conditions on the result, compare with quoted strings too, for example [Customer:Status] == "Active".
• Long scripts. Keep transactions short to avoid blocking other users. Raise Query Timeout only when you need to.
• Security. Only low-code engineers should write SQL. Never put user input straight into the query without quotes, and prefer Bind Tokens.

Examples

tip

To understand how to use the below examples, please see Running Examples.

1. Load a customer by ID

This action loads one customer, using a bind token for the ID. The columns are then available as tokens, for example [Customer:Email] and [Customer:Name].

{
    "Title": "Run SQL Query",
    "ActionType": "RunSql",
    "Description": "Load the selected customer",
    "Parameters": {
        "SqlQuery": "SELECT CustomerId, Name, Email, Status FROM Customers WHERE CustomerId = @CustomerId",
        "BindTokens": [
            {
                "name": "CustomerId",
                "value": "[CustomerId]"
            }
        ],
        "OutputTokenName": "Customer"
    }
}

2. Insert an order and get the new ID

This action inserts a row and returns the new identity value. [NewOrderId] holds the ID, because it's the first column of the first row.

{
    "Title": "Run SQL Query",
    "ActionType": "RunSql",
    "Description": "Insert the order",
    "Parameters": {
        "SqlQuery": "INSERT INTO Orders (CustomerId, Total, CreatedOn) VALUES (@CustomerId, @Total, GETDATE());\nSELECT CAST(SCOPE_IDENTITY() AS int) AS OrderId",
        "BindTokens": [
            {
                "name": "CustomerId",
                "value": "[CustomerId]"
            },
            {
                "name": "Total",
                "value": "[Total]"
            }
        ],
        "OutputTokenName": "NewOrderId"
    }
}

3. Save a count and a date in tokens of your choice

This action counts a customer's orders and finds the latest order date. The values go into [OrderCount] and [LastOrderDate]. Because LastOrder is a date column, [LastOrderDate:ISODate] is also available.

{
    "Title": "Run SQL Query",
    "ActionType": "RunSql",
    "Description": "Count the customer's orders",
    "Parameters": {
        "SqlQuery": "SELECT COUNT(*) AS Total, MAX(CreatedOn) AS LastOrder FROM Orders WHERE CustomerId = @CustomerId",
        "BindTokens": [
            {
                "name": "CustomerId",
                "value": "[CustomerId]"
            }
        ],
        "ExtractColumns": [
            {
                "name": "Total",
                "value": "OrderCount"
            },
            {
                "name": "LastOrder",
                "value": "LastOrderDate"
            }
        ]
    }
}

4. Update another database and handle errors

This action updates a table in a reporting database. ReportingDb is the name of a connection string in web.config. The query can run for up to 30 minutes. If it fails, the error is logged and the remaining actions still run.

{
    "Title": "Run SQL Query",
    "ActionType": "RunSql",
    "Description": "Refresh the reporting summary",
    "Parameters": {
        "ConnectionString": "ReportingDb",
        "QueryTimeout": "1800",
        "UseTransactions": true,
        "SqlQuery": "EXEC dbo.RefreshSalesSummary @Year = @Year",
        "BindTokens": [
            {
                "name": "Year",
                "value": "[Year]"
            }
        ],
        "OnError": [
            {
                "Title": "Log Error",
                "ActionType": "LogError",
                "Parameters": {
                    "Message": "Sales summary refresh failed: [ExceptionMessage]"
                }
            }
        ]
    }
}

Revised 09/26/2026