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: This function takes a table-valued parameter (TVP), and produces a result set containing the cross product of the parameter table with itself. … Continue reading How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ →

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

Posted on April 25, 2023 by lukaseder

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
BEGIN
INSERT INTO @result
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

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

• Facebook
• LinkedIn
• X
• Reddit
• More

Like this:

Like Loading...

###
Published by lukaseder

I made jOOQ View all posts by lukaseder

Original source