Access from Flow Studio — SQL Server Guide

The Two SQL Nodes

Flow Studio provides two execution nodes for SQL Server interaction, both under the Datasources capability:

SqlQueryNode

Executes a SELECT query and returns the result set as a JSON array bound to an output variable. Use for all read operations.

SqlUpdateNode

Executes an INSERT, UPDATE, or DELETE command and returns the rows affected count. Use for all write operations.

SqlQueryNode — Reading Data

Configuration Properties

Property	Required	Description
`datasourceId`	Yes	The registered datasource identifier (e.g., `customer-data-db`)
`query`	Yes	Parameterized SQL SELECT statement. Use `@paramName` placeholders — never string concatenation.
`parameters`	No	Object mapping parameter names to workflow variable expressions
`outputVariable`	Yes	Name of the workflow variable to store the result set (JSON array)
`firstRowOnly`	No	If true, output is a single object rather than an array (for single-row lookups)
`commandTimeoutSeconds`	No	Override the datasource default timeout for this node only

Example: Single Record Lookup

{
  "nodeType": "SqlQueryNode",
  "nodeId": "fetch-customer",
  "datasourceId": "customer-data-db",
  "query": "SELECT CustomerId, Name, Email, Status, ClassificationLabel FROM Customer WHERE TenantId = @tenantId AND CustomerId = @customerId AND IsDeleted = 0",
  "parameters": {
    "tenantId": "{{workflow.tenantId}}",
    "customerId": "{{input.customerId}}"
  },
  "firstRowOnly": true,
  "outputVariable": "customer"
}

// After execution, workflow context has:
// customer.CustomerId, customer.Name, customer.Email, etc.

Example: List Query with Filtering

{
  "nodeType": "SqlQueryNode",
  "nodeId": "list-active-customers",
  "datasourceId": "customer-data-db",
  "query": "SELECT CustomerId, Name, Email, ClassificationLabel FROM Customer WHERE TenantId = @tenantId AND Status = @status AND IsDeleted = 0 ORDER BY Name OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY",
  "parameters": {
    "tenantId": "{{workflow.tenantId}}",
    "status": "{{input.statusFilter}}",
    "offset": "{{input.offset}}",
    "pageSize": "{{input.pageSize}}"
  },
  "outputVariable": "customers"
}

// After execution:
// customers = [ { "CustomerId": "...", "Name": "...", ... }, ... ]

SqlUpdateNode — Writing Data

Configuration Properties

Property	Required	Description
`datasourceId`	Yes	The registered datasource identifier
`command`	Yes	Parameterized INSERT, UPDATE, or DELETE statement
`parameters`	No	Object mapping parameter names to workflow variable expressions
`outputVariable`	No	Variable to store the rows-affected count (integer)
`returnInsertedId`	No	If true and the command is an INSERT, returns the generated identity/GUID value

Example: INSERT New Record

{
  "nodeType": "SqlUpdateNode",
  "nodeId": "create-customer",
  "datasourceId": "customer-data-db",
  "command": "INSERT INTO Customer (CustomerId, TenantId, Name, Email, Industry, Status, CreatedBy, CreatedAt, UpdatedAt) VALUES (NEWID(), @tenantId, @name, @email, @industry, 'Active', @createdBy, GETUTCDATE(), GETUTCDATE())",
  "parameters": {
    "tenantId": "{{workflow.tenantId}}",
    "name": "{{input.customerName}}",
    "email": "{{input.customerEmail}}",
    "industry": "{{input.industry}}",
    "createdBy": "{{workflow.userId}}"
  },
  "outputVariable": "rowsAffected"
}

Example: Soft Delete

{
  "nodeType": "SqlUpdateNode",
  "nodeId": "delete-customer",
  "datasourceId": "customer-data-db",
  "command": "UPDATE Customer SET IsDeleted = 1, DeletedAt = GETUTCDATE(), UpdatedAt = GETUTCDATE() WHERE TenantId = @tenantId AND CustomerId = @customerId AND IsDeleted = 0",
  "parameters": {
    "tenantId": "{{workflow.tenantId}}",
    "customerId": "{{input.customerId}}"
  },
  "outputVariable": "rowsAffected"
}

Parameter Binding Syntax

Parameter values in SQL nodes support the standard workflow expression syntax:

Expression	Source	Example
`{{workflow.tenantId}}`	Workflow execution context	Current tenant's integer ID
`{{workflow.userId}}`	Workflow execution context	ID of the user who triggered the workflow
`{{input.fieldName}}`	Workflow trigger input payload	Value from a form submission or API request body
`{{variables.varName}}`	Workflow variable store	Output from a previous node
`{{nodes.nodeId.outputField}}`	Named node output	Field from a prior node's result

Never Use String Concatenation for SQL Parameters

SQL nodes enforce parameterized queries. The query/command string is compiled with @paramName placeholders and parameters are bound via SqlParameter objects — never concatenated into the SQL string. Attempting to inject raw values into the SQL string will result in a validation error at workflow design time.

Error Handling in SQL Nodes

SQL nodes surface errors through the standard Flow Studio error handling mechanism. Common error scenarios:

Connection failure: The node retries up to maxRetryCount times with exponential back-off before failing the workflow step.
Query timeout: Node raises a SqlTimeoutException — handle via a fault edge in the workflow graph.
No rows found: firstRowOnly: true returns null — check for null before accessing fields.
Constraint violation: INSERT/UPDATE violating a unique constraint raises a SqlException with error code 2627.

← AI-Ready Schema Design Next: AI Form Generation →