I have a stored Procedure which takes table valued parameter as input. I am using JDBI in my SpringBoot Application. How do I bind TVP to stored procedure. This is my Stored Procedure.
CREATE PROCEDURE exampleSP
@country char(2),
@store int,
@tags as dbo.udtTags READONLY,
AS
BEGIN
select * from sales where country = @country AND store = @store AND id in (select ids from @tags)
END
This is my User Defined Table.
CREATE TYPE udtTags AS TABLE
(
ids INT
)
GO
This is what I have tried:
List<Integer> idList = new ArrayList<>();
idList.add(1);
idList.add(2);
return jdbi.withHandle(handle -> {
OutParameters result = handle.createCall("{call exampleSP(:country, :store, :ids)}")
.bind("country", "US")
.bind("store", 1)
.bindList("ids", idList)
.invoke();
return null;
});
This is giving error: org.jdbi.v3.core.statement.UnableToCreateStatementException: Missing named parameter 'ids' in binding:{positional:{}, named:{__ids_1:1,store:1,country:US,__ids_0:2, finder:[]}