- 2 minutes to read

Formula - sqllookup

The sqllookup Formula function looks up the content value in a SQL Server reference table and returns the value of another column from the matching row, for example the area code for a city name.

Prerequisites

  • A SQL DB Lookup entry exists in the Nodinite environment. Add it in the Portal under Configure SQL DB Lookup: its Name is the first sqllookup parameter, and its Connection String points to the reference database.
  • The account in the connection string can read the database and table you look up in.
  • The network allows access from the Nodinite servers to that SQL Server instance.

Syntax 

sqllookup('connectionStringId', 'database', 'table', 'searchColumn', 'resultColumn', content)

Parameters

The parameters are positional; give all six, in this order.

Parameter Required Description
'connectionStringId' Yes The Name of the SQL DB Lookup entry, such as 'SqlDbLookupLocal'. Don't rename an entry that expressions already use.
'database' Yes The database that holds the reference table.
'table' Yes The reference table, such as 'AreaCodes'.
'searchColumn' Yes The column to compare with the content value, such as 'Name'.
'resultColumn' Yes The column whose value is returned, such as 'AreaCode'.
content Yes The value to look up: body, context, or the result of another function (see Formula language rules).

Returns

The resultColumn value of the row whose searchColumn equals the content value.

Examples

All examples use this reference table in the database Directory:

CREATE TABLE dbo.AreaCodes (
  Name      NVARCHAR(128) NOT NULL,
  AreaCode  NVARCHAR(8)   NOT NULL
);

INSERT INTO dbo.AreaCodes (Name, AreaCode) VALUES ('KARLSTAD', '054'), ('STOCKHOLM', '08');

Example 1: City name in the body to area code

Input 

KARLSTAD

Formula 

sqllookup('SqlDbLookupLocal', 'Directory', 'AreaCodes', 'Name', 'AreaCode', body)

Result 

054

Example 2: City name in a context property to area code

Input

Key Value
city STOCKHOLM

Formula 

sqllookup('SqlDbLookupLocal', 'Directory', 'AreaCodes', 'Name', 'AreaCode', context('city'))

Result 

08

Notes

  • Each evaluation queries SQL Server, during logging and during re-index. Index the search column (for example CREATE NONCLUSTERED INDEX IX_AreaCodes_Name ON dbo.AreaCodes(Name);) and keep the table compact.
  • Use a read-only account in the connection string.

Next Steps