Use Jooq’s Code Generator to Easily Pass Table-Valued Parameters

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 processed to a stored processed.

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 Produce a Result Set Containing The Cross Product of the Parameter Table with Itslf. 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 the table-valued parameters tutorials and use a com.microsoft.sqlserver.jdbc.SQLServerDataTableBut if you’re using jooq and its code generator, bot the user-defined type and the function will have generated java code for you to call easy:

List l = List.of(1, 2, 3);
Result 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 eg 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-primeported method that models an embedded call to a Table-Valied Function (Standalone Calls are also possible)
• UNumberTableRecord is a record representing the user-defined type u_number_tablewhich 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!