[MS SQL] SPRING JDBC

 -- 使用JDBC連線
http://www.codedata.com.tw/java/java-tutorial-the-3rd-class-2-spring-jdbc/

JDBCDataSource dataSource = new JDBCDataSource();

dataSource.setUrl("jdbc:hsqldb:file:src/main/resources/db/dvd_library");

dataSource.setUser("codedata");

dataSource.setPassword("123456");

-- 呼叫 PROCEDURE
 http://stackoverflow.com/questions/9361538/spring-jdbc-template-for-calling-stored-procedures

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
    .withSchemaName(schema)
    .withCatalogName(package)
    .withProcedureName(procedure)();
...
jdbcCall.addDeclaredParameter(new SqlParameter(paramName, OracleTypes.NUMBER));
...
jdbcCall.execute(callParams);

-- procedure 回傳 temp table

http://stackoverflow.com/questions/1443663/how-to-return-temporary-table-from-stored-procedure

 -- The "called" SP
  declare
      @returnAsSelect bit = 0;

  if object_id('tempdb..#GetValuesOutputTable') is null
  begin
      set @returnAsSelect = 1;
      create table #GetValuesOutputTable(
         ...   
      );
  end

  -- populate the table

  if @returnAsSelect = 1
      select * from #GetValuesOutputTable;