0

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:[]}

0 Answers0