How should I call the following stored procedure(sqlserver 2008 r2) in springboot, mybatis with annotation
declare @p1 TbType_Order
insert into @p1 values('00005',20.01)
insert into @p1 values('00006',15.99)
exec Gp_UpdateOrderBillDT @tb=@p1,@intFlag=0,@billNumberId=16,@billType=301
the "TbType_Order" is a User-Defined Table Type
CREATE TYPE [dbo].[TbType_Order] AS TABLE(
[No] [varchar](50) NULL,
[Price] [numeric](24, 10) NULL
)
GO
I try to call using this way:
public interface BillProcedureMapper {
@Select({
"<script>" +
" declare @p1 TbType_Order " +
" <foreach item=\"item\" collection=\"details\"> ",
" insert into @p1 values( #{item.no},#{item.price} )" +
" </foreach> ",
" exec Gp_UpdateOrderBillDT " +
"@tb=@p1," +
"@intFlag=0," +
"@billNumberId=#{billNumberId}," +
"@billType=301; " +
"</script>"
})
@Options(statementType = StatementType.CALLABLE)
void savePurchaseOrderDetail(
@Param("billNumberId") Long billNumberId,
@Param("details") List<DetailDTO> details
);
}
but jdbc say "variable @p1 already declared"
and I try use type handler :
public class TableTypeOrderHandler extends BaseTypeHandler {
@Override
public void setNonNullParameter(
PreparedStatement ps, int i, Object parameter, JdbcType jdbcType
) throws SQLException {
SQLServerDataTable sourceDataTable = new SQLServerDataTable();
sourceDataTable.addColumnMetadata("No", Types.VARCHAR);
sourceDataTable.addColumnMetadata("Price", Types.DECIMAL);
List<DetailDTO> dataList = (List<DetailDTO>) parameter;
for (DetailDTO o : dataList) {
sourceDataTable.addRow(
o.getNo(),
o.getPrice()
);
}
ps.setObject(i, sourceDataTable);
}
@Override
public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
return null;
}
@Override
public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return null;
}
@Override
public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return null;
}
}
then change the select method:
@Select({
"exec Gp_UpdateOrderBillDT " +
"@tb=#{details, jdbcType=VARCHAR, typeHandler=com.xxx.TableTypeOrderHandler}," +
"@intFlag=0," +
"@billNumberId=#{billNumberId}," +
"@billType=301; "
})
@Options(statementType = StatementType.CALLABLE)
void savePurchaseOrderDetail(
@Param("billNumberId") Integer billNumberId,
@Param("details") List<DetailDTO> details
);
but jdbc exception is: Operand type conflict: table type is not compatible with int
What should I do to get it right?