Executing queries over TDS

In this topic, you'll learn how to use Tabular Data Stream (TDS) endpoints to execute stored functions and queries authored using the Kusto Query Language (KQL).

Executing stored functions

You can create and execute stored functions in a similar way to SQL stored procedures.

For example, if you have a stored function as described in the table, you can execute it using the following code:

Name	Parameters	Body	Folder	DocString
MyFunction	(myLimit: long)	{StormEvents | limit 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, make sure you execute stored functions with an explicit reference to the kusto schema. In the example, the stored function is executed using kusto.Myfunction.

You can also call stored functions from Transact-SQL (T-SQL) in a similar way to SQL stored procedures. Creating optimized Kusto Query Language queries and encapsulating them in stored functions, helps you to minimizes your T-SQL code.

For example, you can call MyFunction using the following code:

SELECT * FROM kusto.MyFunction(10)

Executing queries

The SQL stored procedure sp_execute_kql can be used to execute Kusto QueryLanguage queries, including parameterized queries. The procedure is similar to the sp_executesql stored procedure.

The first parameter of sp_execute_kql is the Kusto QueryLanguage query. Additional parameters are treated as query parameters.

For example:

  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 | limit 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.