---
title: "Tools.ExecSql"
slug: "toolsexecsql"
updated: 2026-02-07T11:01:59Z
published: 2026-02-07T11:01:59Z
canonical: "support.lusid.com/toolsexecsql"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.lusid.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Tools.ExecSql

| **Type** | **Read/write** | **Author** | **Availability** |
| --- | --- | --- | --- |
| [Direct provider](/v1/docs/what-are-a-data-provider-and-a-direct-provider) | Read | Finbourne | Provided with LUSID |

The `Tools.ExecSql` provider enables you to write a [Luminesce query](/v1/docs/understanding-the-luminesce-sql-query-syntax) that constructs SQL using string concatenation, and then executes it. This has many uses, such as allowing you to build generalised queries that can be easily adapted, programmatically build views based on the current state of the system and more.

Note that whilst [Sys.Admin.SetupView](/v1/docs/using-sysadminsetupview-to-create-custom-views) can also achieve the same result, it is more complex to use and has the power to create views, meaning it is likely to only be accessible to a small set of users. `Tools.ExecSql` only runs SQL that the user can already run, meaning it can be granted to all users without significant concerns.

**See also:** [Sys.Admin.SetupView](/v1/docs/using-sysadminsetupview-to-create-custom-views)

## Basic usage

```sql
@@sql = select 'select <column> from <some-provider> where <filter-expression>';

@x = use Tools.ExecSql with @@sql
    {@@sql} 
enduse;
select * from @x;
```

## Input tables

`Tools.ExecSql` takes in at least one scalar variable with optional additional full tables of data, and outputs one table of data containing the executed string of SQL.

## Examples

> **Note:** For more example Luminesce queries, visit our [GitHub repo](https://github.com/finbourne/luminesce-examples/tree/master/examples).

### Example 1: Executing a simple string of SQL

```sql
@@sql = select 'select 1 as column1';

@x = use Tools.ExecSql with @@sql
    {@@sql} 
enduse;

select * from @x;
```

The table of data returned looks like this:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/e0db7e5f-b023-4a93-b9f9-354c3968f3c0.png)

### Example 2: Executing a string of SQL containing a table variable

In this example, `Tools.ExecSql` is used with a `#PARAMETERVALUE` macro to select data from a table which could vary in shape.

```sql
@table_of_data = select 'this' as SomeText, 'that' as MoreText, 10 as SomeNumber;
    
@x = use Tools.ExecSql with @table_of_data
    select * from #PARAMETERVALUE(@table_of_data)
enduse;
select * from @x
```

The table of data returned by the query looks like this, populated with the `#PARAMETERVALUE` macro values:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/564122b4-641e-479a-b922-9bd84af2769b.png)

### Example 3: Executing a string of SQL containing a `#PARAMETERVALUE` macro

```sql
@data = select 'this' as SomeText, 'that' as MoreText, 10 as SomeNumber;
@@sql = select 'select * from #PARAMETERVALUE(@data)';
    
@x = use Tools.ExecSql with @@sql, @data
    {@@sql}
enduse;
select * from @x
```

The table of data returned by the query looks like this, populated with the `#PARAMETERVALUE` macro values: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/07a384d7-92a2-4b9f-bb7f-c0d8d5bf2b40.png)

### Example 4: Nesting a `Tools.ExecSql` statement within a custom view

In this example, we create a custom view which uses `Tools.ExecSql` to ensure the string of SQL is executed at a specific point in the query. `Tools.ExecSql` requires no separators and takes in no options, allowing you to easily nest a `Tools.ExecSql` statement within a larger query.

`Tools.ExecSql` also supports being run inside an [--allowExecuteIndirectly](/v1/docs/using-sysadminsetupview-to-create-custom-views#allowExecuteIndirectly) custom view. This option is also applied to what `Tools.ExecSql` itself runs. This means you can create a provider that allows some users to interact with other providers they otherwise cannot access, including the use of SQL-as-a-string.

```sql
@x = use Sys.Admin.SetupView
--provider=Views.ExecSql.Nesting
--distinct
--filter=Isin is not null
--limit=42
--allowExecuteIndirectly
----

@@sql = select 'select
  #DISTINCT
  #SELECT { { Isin : Isin } }
  from Lusid.Instrument where
  #RESTRICT { { Isin : Isin } }
#LIMIT';
   
@y = use Tools.ExecSql with @@sql
  {@@sql}
enduse;

select * from @y;

enduse;

select * from @x
```

### Example 5: Passing a provider name into a custom view using `Tools.ExecSql`

In this example, we create a custom view which takes in a provider name as a parameter. `Tools.ExecSql` is used to execute some SQL using the table of data from the named provider. The data from this execution is then used with [Notification.ManualEvent.Writer](/v1/docs/notificationmanualeventwriter) to create a new manual event in the Notification Service.

Whilst this example uses `Lusid.Portfolio` as an input parameter to create a manual event, you might instead choose to pass in the name of a custom view to, for example, perform a data quality check.

```sql
@x = use Sys.Admin.SetupView
--provider=Views.ExecSomeSql
--description="Using the Tools.ExecSql provider to pass in a provider name as a custom view input parameter"
--parameters
ViewName,Text,Lusid.Portfolio,false
----

@@ViewName = select #PARAMETERVALUE(ViewName);

@data = use Tools.ExecSql with @@ViewName
select * from {@@ViewName}
    where PortfolioType = 'Transaction';

enduse;

@data_to_write = select 
  PortfolioScope as Subject,
  PortfolioCode as Message 
from @data;

select * from Notification.ManualEvent.Writer
    where ToWrite = @data_to_write;

enduse;

select * from @x
```