-
Notifications
You must be signed in to change notification settings - Fork 423
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. |