Passing a user-defined table type to a SQL Server stored procedure using JDBC

java, jdbc, sql-server, stored-procedures, user-defined-types

Solution

Yes, it is now possible. Version 6.0 of Microsoft's JDBC driver for SQL Server added support for table-valued parameters.

The following code sample shows how to

- use a `SQLServerDataTable` object to hold the table data to be passed, and

- call the `SQLServerCallableStatement#setStructured` method to pass that table to the stored procedure.

SQLServerDataTable sourceDataTable = new SQLServerDataTable();   
sourceDataTable.addColumnMetadata("SDate", java.sql.Types.DECIMAL);
sourceDataTable.addColumnMetadata("EDate", java.sql.Types.DECIMAL);
sourceDataTable.addColumnMetadata("PlantCode", java.sql.Types.NVARCHAR);
sourceDataTable.addColumnMetadata("LoadType", java.sql.Types.NCHAR);
sourceDataTable.addColumnMetadata("Asset", java.sql.Types.BIGINT);

// sample data
sourceDataTable.addRow(123, 234, "Plant1", "Type1", 123234);   
sourceDataTable.addRow(456, 789, "Plant2", "Type2", 456789);   

try (CallableStatement cs = conn.prepareCall("{CALL dbo.RegisterInitAssets (?)}")) {
    ((SQLServerCallableStatement) cs).setStructured(1, "dbo.INITVALS_MSG", sourceDataTable);
    boolean resultSetReturned = cs.execute();
    if (resultSetReturned) {
        try (ResultSet rs = cs.getResultSet()) {
            rs.next();
            System.out.println(rs.getInt(1));
        }
    }
}

For more details, see the following MSDN article:

Using Table-Valued Parameters

Problem

We've got this User-Defined-Table Type in SQL Server: ``` CREATE TYPE [dbo].[INITVALS_MSG] AS TABLE( [SDate] [decimal](8, 0) NOT NULL, [EDate] [decimal](8, 0) NOT NULL, [PlantCode] [nvarchar](10) NOT NULL, [LoadType] [nchar](8) NOT NULL, [Asset] [bigint] NOT NULL ) ``` and a Stored Procedure which takes that table as input: ``` ALTER PROCEDURE [dbo].[RegisterInitAssets] @initmsg INITVALS_MSG ReadOnly AS BEGIN ... ``` Now, I need to call this procedure from java. Is it possible to do such a thing? Does JDBC support this? --------EDIT I have a corresponding class in java for that type: ``` public class DBInitialAsset { private Integer sDate; private Integer eDate; private String plantCode; private String loadType; private Integer asset; public DBInitialAsset() { } } ```

Original source