Skip to content

Using TVP with stored procedure in Spring framework

Afsaneh Rafighi edited this page Feb 6, 2017 · 1 revision

As of 6.1.1, the Microsoft JDBC Driver for SQL Server supports using table-valued parameters with stored procedure for Spring framework. Two public APIs are added to set the TVP name when calling a stored procedure having a table valued parameter passed as input.

Example: Having a table defined as:

CREATE TABLE [dbo].[customer] (ID int)

Having a stored procedure defined as:

CREATE PROCEDURE [dbo].[SProcTVP] @InputData      dbo.TVPName READONLY 
AS 
BEGIN 
INSERT INTO [test].[dbo].[customer] 
SELECT ID FROM @InputData 
END
Connection conn = dataSource.getConnection();
dataSource.getConnection();
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

String sql = "{call dbo.SProcTVP(:inputData)}";
SQLServerDataTable inputDataTable = new SQLServerDataTable();
inputDataTable.setTvpName("[dbo].[TVPName]");
inputDataTable.addColumnMetadata("ID", Types.INTEGER);
inputDataTable.addRow(1);
MapSqlParameterSource source = new MapSqlParameterSource();
source.addValue("inputData", inputDataTable);
namedParameterJdbcTemplate.update(sql, source);
Name Description
public void setTvpName(String tvpName) Sets the TVP name for the given data table
public String getTvpName() Retrieves the TVP name of data table.
Clone this wiki locally