Edit

Share via

Run KQL queries and stored functions with SQL Server emulation

  • Article

Azure Data Explorer provides a Tabular Data Stream (TDS) endpoint that allows you to query data in a way similar to how you would query data in SQL Server. The endpoint supports TDS versions 7.x and 8.0.

In this article, you'll learn how to run stored functions and Kusto Query Language (KQL) queries from an SQL compatible client.

Note

The information in this article applies to running parameterized calls over the TDS protocol, also known as RPC calls.

For more information, see the overview on SQL Server emulation in Azure Data Explorer.

Run KQL queries

The SQL stored procedure sp_execute_kql can be used to run KQL queries, including parameterized queries. The procedure is similar to the sp_executesql stored procedure.

Note

The sp_execute_kql procedure can only be called via an RPC call as shown in the following example and not from within a regular SQL query.

The first parameter of sp_execute_kql is the KQL query, and any other parameters are treated as query parameters. The following example shows how to use sp_execute_kql.

  using (var connection = new SqlConnection(csb.ToString()))
  {
    await connection.OpenAsync();
    using (var command = new SqlCommand("sp_execute_kql", connection))
    {
      command.CommandType = CommandType.StoredProcedure;
      var query = new SqlParameter("@kql_query", SqlDbType.NVarChar);
      command.Parameters.Add(query);
      var parameter = new SqlParameter("mylimit", SqlDbType.Int);
      command.Parameters.Add(parameter);
      query.Value = "StormEvents | take myLimit";
      parameter.Value = 3;
      using (var reader = await command.ExecuteReaderAsync())
      {
        // Read the response.
      }
    }
  }

Note

When calling sp_execute_kql via TDS, parameter types are set by the protocol and don't need to be declared.

Call stored functions

You can create and call stored functions like SQL stored procedures. For example, if you have a stored function as described in the following table, you can call it as shown in the code example.

Name Parameters Body Folder DocString
MyFunction (myLimit: long) {StormEvents | take myLimit} MyFolder Demo function with parameter 
  using (var connection = new SqlConnection(csb.ToString()))
  {
    await connection.OpenAsync();
    using (var command = new SqlCommand("kusto.MyFunction", connection))
    {
      command.CommandType = CommandType.StoredProcedure;
      var parameter = new SqlParameter("mylimit", SqlDbType.Int);
      command.Parameters.Add(parameter);
      parameter.Value = 3;
      using (var reader = await command.ExecuteReaderAsync())
      {
        // Read the response.
      }
    }
  }

Note

To distinguish between stored functions and emulated SQL system stored procedures, call stored functions with an explicit reference to the kusto schema. In the example, the stored function is called using kusto.Myfunction.

Related content