Using table-valued parameter with stored procedures

Question

I'm trying to apply functionality to my existing rest client that allows you to send a list of "location ids" as comma separated valued. My idea was to pass the id's into a table-valued parameter (TVP), but i can't figure out how to implement it. I am using

new SimpleJdbcCall(getDataSource())
            .withoutProcedureColumnMetaDataAccess().withProcedureName(procedureName)
            .declareParameters(new SqlParameter(PARAMETER_COMPANY_ID, Types.VARCHAR))
            .declareParameters(new SqlParameter(PARAMETER_MIN_YEAR, Types.INTEGER))
            .declareParameters(new SqlParameter(PARAMETER_MIN_WEEK, Types.INTEGER))
            .declareParameters(new SqlParameter(PARAMETER_LOCATION_ID, ???));

I don't know what to parse in to the last parameter hence the "???". I am creating a procedure as show below and the select statement has a "WHERE field IN (SELECT * FROM LocationIDs) .. LocationIDs is a "User-Defined table type" with one column "LocationID".

CREATEPROCEDURE [dbo].[PROCEDURE_NAME]
@COMPANY_ID varchar(50),
@MIN_YEAR INT,
@MIN_WEEK INT,
@LOCATION_ID LocationIDs READONLY

Hope someone can help me :)

Using table-valued parameter with stored procedures

0 Answers0