Skip to main content

Execute Query

Executes a SQL Server SELECT query and returns the results as a table.

Common Properties​

  • Name - The custom name of the node.
  • Color - The custom color of the node.
  • Delay Before (sec) - Waits in seconds before executing the node.
  • Delay After (sec) - Waits in seconds after executing node.
  • Continue On Error - Automation will continue regardless of any error. The default value is false.
info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs​

  • Connection Id - The unique identifier of the database connection to use (optional if credentials provided directly).
  • Transaction Id - The unique identifier of a transaction (optional). Use when the query is part of a database transaction.
  • SQL Query - The SQL SELECT statement to execute. Supports mustache template variables (e.g., {{userId}}).

Options​

  • Command Timeout - Command execution timeout in seconds (default: 30). Increase for long-running queries.
  • Credentials - SQL Server database credentials (optional). Use this if you want to execute the query without a Connect node.

Output​

  • Result - The query results returned as a table structure with columns and rows.

How It Works​

The Execute Query node executes a SQL SELECT statement and returns the results. When executed, the node:

  1. Retrieves the database connection (either from connection ID or creates new connection from credentials)
  2. Optionally retrieves the transaction if a transaction ID is provided
  3. Processes mustache template variables in the SQL query using the message context
  4. Decodes any HTML entities in the rendered query
  5. Creates a SqlCommand with the SQL query and timeout settings
  6. Executes the query using ExecuteReader
  7. Loads the results into a DataTable
  8. Converts the DataTable to a table structure with columns and rows
  9. Returns the table structure as output

Requirements​

  • Either: A valid connection ID from Connect node OR valid database credentials
  • Valid SQL SELECT statement
  • If using transactions, a valid transaction ID from Start Transaction node
  • Appropriate SELECT permission on the queried tables

Error Handling​

The node will return specific errors in the following cases:

  • ErrInvalidArg - Neither connection ID nor credentials provided, or command timeout is invalid
  • ErrNotFound - Connection ID not found in connections dictionary
  • ErrConnection - Cannot connect to SQL Server (when using credentials mode)
  • ErrRuntime - Query execution failed (syntax error, table doesn't exist, timeout, etc.)

Usage Examples​

Basic SELECT Query​

SELECT * FROM Customers WHERE Status = 'Active'

Query with Template Variables​

SELECT CustomerID, CustomerName, Email, City
FROM Customers
WHERE City = '{{city}}' AND Country = '{{country}}'

In your message context:

{
  "city": "New York",
  "country": "USA"
}

JOIN Query​

SELECT
  o.OrderID,
  o.OrderDate,
  c.CustomerName,
  p.ProductName,
  od.Quantity,
  od.UnitPrice
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
WHERE o.OrderDate >= '{{startDate}}'
ORDER BY o.OrderDate DESC

Aggregate Query​

SELECT
  Category,
  COUNT(*) AS TotalProducts,
  AVG(Price) AS AveragePrice,
  SUM(Stock) AS TotalStock,
  MIN(Price) AS MinPrice,
  MAX(Price) AS MaxPrice
FROM Products
GROUP BY Category
HAVING COUNT(*) > 5
ORDER BY AveragePrice DESC

Query with Subquery​

SELECT CustomerName, TotalOrders, TotalRevenue
FROM (
  SELECT
    c.CustomerName,
    COUNT(o.OrderID) AS TotalOrders,
    SUM(o.TotalAmount) AS TotalRevenue
  FROM Customers c
  LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
  GROUP BY c.CustomerID, c.CustomerName
) AS CustomerStats
WHERE TotalOrders > {{minOrders}}
ORDER BY TotalRevenue DESC

Common Table Expression (CTE)​

WITH RegionalSales AS (
  SELECT
    Region,
    SUM(SalesAmount) AS TotalSales
  FROM Sales
  WHERE SalesDate >= '{{startDate}}'
  GROUP BY Region
)
SELECT
  Region,
  TotalSales,
  CAST(TotalSales * 100.0 / SUM(TotalSales) OVER() AS DECIMAL(5,2)) AS PercentOfTotal
FROM RegionalSales
ORDER BY TotalSales DESC

Common Use Cases​

  1. Data Retrieval - Fetch customer information, product details, order history
  2. Business Intelligence - Generate sales reports, inventory summaries, analytics
  3. Data Validation - Check if records exist, verify data integrity
  4. Dashboard Data - Retrieve metrics and KPIs for dashboards
  5. Export Operations - Extract data for export to Excel, CSV, or other formats
  6. Search Functionality - Implement search features in automation workflows
  7. Conditional Processing - Query data to determine next automation steps
  8. Data Synchronization - Read data from SQL Server to sync with other systems

Working with Query Results​

Access Results in JavaScript​

// Query results are in $item.result
const results = $item.result;

// Access table structure
const columns = results.columns;  // Array of column names
const rows = results.rows;        // Array of row objects

// Iterate through rows
rows.forEach(row => {
  console.log(`Customer: ${row.CustomerName}, Email: ${row.Email}`);
});

// Get first row
const firstRow = rows[0];

// Count rows
const rowCount = rows.length;

// Check if results are empty
if (rows.length === 0) {
  console.log('No results found');
}

// Access specific columns
const customerNames = rows.map(row => row.CustomerName);

Use with Loop Node​

Connect the Query node to a Loop node to process each row:

Query → Loop (over $item.result.rows)
  └─ Process each row individually

Filter and Transform Results​

// Filter results in JavaScript
const activeCustomers = $item.result.rows.filter(row => row.Status === 'Active');
const highValueOrders = $item.result.rows.filter(row => row.Total > 1000);

// Transform results
const emailList = $item.result.rows.map(row => ({
  email: row.Email,
  name: row.CustomerName
}));

// Group results
const groupedByCity = $item.result.rows.reduce((acc, row) => {
  if (!acc[row.City]) acc[row.City] = [];
  acc[row.City].push(row);
  return acc;
}, {});

Tips for Effective Use​

  • Template Variables - Use mustache syntax {{variable}} to make queries dynamic and secure
  • SQL Editor - The node provides syntax highlighting for SQL statements
  • Query Optimization - Use WHERE clauses to limit results, add indexes to improve performance
  • Column Selection - Select only needed columns instead of using SELECT *
  • Limit Results - Use TOP clause for large datasets to prevent memory issues
  • Direct Credentials Mode - Use the optional Credentials field to query without a Connect node
  • Transaction Support - Include queries in transactions for data consistency
  • Result Validation - Always check if results are empty before processing
  • Command Timeout - Increase timeout for complex queries that take longer to execute
  • Parameterization - Use template variables instead of string concatenation to prevent SQL injection

Performance Best Practices​

Optimize Queries​

-- GOOD: Specific columns, WHERE clause, TOP
SELECT TOP 100 CustomerID, CustomerName, Email
FROM Customers
WHERE Status = 'Active'
  AND CreatedDate >= '{{startDate}}'

-- AVOID: SELECT * without conditions
SELECT * FROM Customers

Use Indexes​

Ensure your SQL Server tables have proper indexes on columns used in:

  • WHERE clauses
  • JOIN conditions
  • ORDER BY clauses
  • GROUP BY clauses

Pagination​

For large datasets, implement pagination:

-- SQL Server 2012+ pagination
SELECT CustomerID, CustomerName, Email
FROM Customers
WHERE Status = 'Active'
ORDER BY CustomerID
OFFSET {{offset}} ROWS
FETCH NEXT {{pageSize}} ROWS ONLY
const pageSize = 100;
const page = 1;
const offset = (page - 1) * pageSize;

Connection Reuse​

Connect (once)
  ↓
Query 1
  ↓
Query 2
  ↓
Query 3
  ↓
Disconnect

Better than connecting/disconnecting for each query.

Execution Plan​

For complex queries, check execution plan in SQL Server Management Studio to identify bottlenecks.

Common Errors and Solutions​

Query Execution Failed​

Error: ErrRuntime: Query execution failed

Common Causes:

  • Syntax error in SQL
  • Table or column doesn't exist
  • Invalid template variable values
  • Permission denied on table
  • Query timeout exceeded

Solutions:

  • Test query in SQL Server Management Studio (SSMS) first
  • Verify table and column names (check case sensitivity in collation)
  • Check template variables are properly substituted
  • Ensure database user has SELECT permission
  • Increase Command Timeout for long-running queries
  • Check execution plan for performance issues

Connection Not Found​

Error: ErrNotFound: Connection not found

Solutions:

  • Ensure Connect node runs before Query node
  • Verify connection ID variable is correct
  • Or use the Credentials option to connect directly

Invalid Object Name​

Error: Invalid object name 'TableName'

Solutions:

  • Verify table name spelling and case
  • Check if schema is specified (use dbo.TableName or schema.TableName)
  • Ensure you're connected to the correct database
  • Use SELECT * FROM sys.tables to list available tables
  • Check if table exists in the database

Invalid Column Name​

Error: Invalid column name 'ColumnName'

Solutions:

  • Verify column name spelling and case
  • Use sp_help 'TableName' to see column names
  • Check for typos in template variables
  • Ensure column exists in the queried table

Command Timeout Expired​

Error: Timeout expired. The timeout period elapsed prior to completion

Solutions:

  • Increase the Command Timeout option (default: 30 seconds)
  • Optimize the query (add indexes, reduce data scanned)
  • Add WHERE clause to limit rows processed
  • Check for table locks or blocking queries
  • Use SQL Server Activity Monitor to identify issues

Arithmetic Overflow Error​

Error: Arithmetic overflow error converting expression to data type

Solutions:

  • Check data type conversions in query
  • Use CAST or CONVERT with appropriate data types
  • Verify numeric calculations don't exceed data type limits
  • Handle NULL values properly in calculations

Advanced Examples​

Dynamic Filtering with Template Variables​

SELECT * FROM Products
WHERE Category = '{{category}}'
  AND Price BETWEEN {{minPrice}} AND {{maxPrice}}
  AND Stock > {{minStock}}
  AND CreatedDate >= '{{startDate}}'
ORDER BY
  CASE WHEN '{{sortBy}}' = 'Price' THEN Price END,
  CASE WHEN '{{sortBy}}' = 'Name' THEN ProductName END

Window Functions​

SELECT
  ProductID,
  ProductName,
  Category,
  Price,
  ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Price DESC) AS PriceRank,
  AVG(Price) OVER (PARTITION BY Category) AS AvgCategoryPrice,
  SUM(Stock) OVER (PARTITION BY Category) AS CategoryTotalStock
FROM Products
WHERE Category IN ({{categoryList}})

Pivot Query​

SELECT *
FROM (
  SELECT Year, Quarter, SalesAmount
  FROM Sales
  WHERE Year = {{year}}
) AS SourceTable
PIVOT (
  SUM(SalesAmount)
  FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable

Recursive CTE​

WITH EmployeeHierarchy AS (
  -- Anchor member
  SELECT EmployeeID, EmployeeName, ManagerID, 0 AS Level
  FROM Employees
  WHERE EmployeeID = {{rootEmployeeId}}

  UNION ALL

  -- Recursive member
  SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
  FROM Employees e
  INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, EmployeeName

JSON Query (SQL Server 2016+)​

SELECT
  CustomerID,
  CustomerName,
  (
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
    FOR JSON PATH
  ) AS OrdersJSON
FROM Customers c
WHERE Country = '{{country}}'
FOR JSON PATH

Temporal Table Query (SQL Server 2016+)​

-- Query historical data
SELECT * FROM Products
FOR SYSTEM_TIME AS OF '{{historicalDate}}'
WHERE Category = '{{category}}'

-- Query changes over time
SELECT * FROM Products
FOR SYSTEM_TIME BETWEEN '{{startDate}}' AND '{{endDate}}'
WHERE ProductID = {{productId}}

Transaction Example​

Connect
  ↓
Start Transaction (get trx_id)
  ↓
Query 1 (read data with isolation, use trx_id)
  ↓
Query 2 (read related data, use trx_id)
  ↓
Non Query (update based on queries, use trx_id)
  ↓
Commit (commit trx_id)
  ↓
Disconnect

Using Direct Credentials (Without Connect)​

Query Node:
  - SQL Query: SELECT * FROM Customers
  - Credentials: [Select Database Credential]
  - Connection Id: (leave empty)

This creates a temporary connection, executes the query, and closes the connection automatically. Useful for one-off queries.

Date and Time Handling​

SQL Server Date Formats​

-- ISO 8601 format (recommended)
SELECT * FROM Orders
WHERE OrderDate >= '2024-01-15T10:30:00'

-- Date only
SELECT * FROM Orders
WHERE CAST(OrderDate AS DATE) = '{{orderDate}}'

-- Date range
SELECT * FROM Orders
WHERE OrderDate >= '{{startDate}}'
  AND OrderDate < DATEADD(day, 1, '{{endDate}}')

Format Dates in Results​

SELECT
  OrderID,
  FORMAT(OrderDate, 'yyyy-MM-dd') AS OrderDateFormatted,
  FORMAT(OrderDate, 'yyyy-MM-dd HH:mm:ss') AS OrderDateTimeFormatted,
  CONVERT(VARCHAR, OrderDate, 120) AS OrderDateISO
FROM Orders

See Also​