How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ

Microsoft T-SQL supports a language feature called table-valued parameter (TVP), which is a parameter of a table type that can be passed to a stored procedure or function.

For example, you may write:

CREATE TYPE u_number_table AS TABLE (column_value INTEGER);

CREATE FUNCTION f_cross_multiply (
  @numbers u_number_table READONLY
)
RETURNS @result TABLE (
  i1 INTEGER,
  i2 INTEGER,
  product INTEGER
)
AS
BEGIN
  INSERT INTO @result
  SELECT
    n1.column_value, 
    n2.column_value, 
    n1.column_value * n2.column_value
  FROM @numbers n1
  CROSS JOIN @numbers n2

  RETURN
END

This function takes a table-valued parameter (TVP), and produces a result set containing the cross product of the parameter table with itself. The function happens to be a table-valued function, but this isn’t strictly necessary. Table-valued parameters can be passed to any function or procedure.

In native T-SQL, the above function can be used as follows:

DECLARE @t u_number_table;
INSERT INTO @t VALUES (1), (2), (3);
SELECT * FROM f_cross_multiply(@t);

Producing the following output:

|i1 |i2 |product|
|---|---|-------|
|1  |1  |1      |
|2  |1  |2      |
|3  |1  |3      |
|1  |2  |2      |
|2  |2  |4      |
|3  |2  |6      |
|1  |3  |3      |
|2  |3  |6      |
|3  |3  |9      |

Calling the function from Java

Using native JDBC, it is possible to follow the table-valued parameters tutorials and use a com.microsoft.sqlserver.jdbc.SQLServerDataTable, but if you’re using jOOQ and its code generator, both the user-defined type and the function will have generated Java code for you to call easily:

List<Integer> l = List.of(1, 2, 3);
Result<FCrossMultiplyRecord> result = ctx
    .selectFrom(fCrossMultiply(new UNumberTableRecord(
        l.stream().map(UNumberTableElementTypeRecord::new).toList()
    )))
    .fetch();

You can imagine more complex queries where the table-valued function is used e.g. in a CROSS APPLY operator.

There are multiple generated objects here:

• FCrossMultiplyRecord is a TableRecord containing the rows produced by the f_cross_multiply function.
• Routines.fCrossMultiply is a static-imported method that models an embedded call to a table-valued function (standalone calls are also possible)
• UNumberTableRecord is a record representing the user-defined type u_number_table, which can be passed as a table valued parameter
• UNumberTableElementTypeRecord is a synthetic record type for a single row of a u_number_table (more complex types with multiple attributes are possible, too!)

Printing this result yields:

+----+----+-------+
|  i1|  i2|product|
+----+----+-------+
|   1|   1|      1|
|   2|   1|      2|
|   3|   1|      3|
|   1|   2|      2|
|   2|   2|      4|
|   3|   2|      6|
|   1|   3|      3|
|   2|   3|      6|
|   3|   3|      9|
+----+----+-------+

Alternatively, just use the generated code to access the result rows like this:

result.forEach(r -> {
    System.out.println(
        r.getI1() + " * " + r.getI2() + " = " + r.getProduct()
    );
});

To get:

1 * 1 = 1
2 * 1 = 2
3 * 1 = 3
1 * 2 = 2
2 * 2 = 4
3 * 2 = 6
1 * 3 = 3
2 * 3 = 6
3 * 3 = 9

Just connect jOOQ’s code generator to your SQL Server database, and start calling your functions accepting table-valued parameters with ease!