dbc调用sqlserver的存储过程时,提示无返回结果集,可以通过在该存储过程中begin后加入set nocount on,就可以读到。
由于这里出现该问题的存储过程过多,而且不属于自己维护,因此,通过写一个代理存储过程,同样加上set nocount on即可读到。
- USE [eduDataDB]
- GO
- /****** Object: StoredProcedure [dbo].[check_test_error] Script Date: 07/29/2013 15:44:47 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER procedure [dbo].[check_test_error](@appType int,@regionAD nvarchar(255),@tablename nvarchar(255))
- as
- begin
- set nocount on
- create table #testTmp
- (
- instanceID uniqueidentifier,
- DID int,
- expressID int
- )
- INSERT INTO #testTmp exec @tablename @appType,@regionAD
- select * from #testTmp
- drop table #testTmp
- end
- public static ExcelTemplateCheckExpress executeGjCheckProcedure(Connection con, String checkTemplateNo,
- ProcedureType procedureParameterOne, String procedureParameterTwo) {
- CallableStatement cstmt = null;
- ResultSet rs = null;
- ExcelTemplateCheckExpress returnObject = new ExcelTemplateCheckExpress();
- StringBuilder sql = new StringBuilder();
- sql.append("{call dbo.check_test_error(?,?,?)}");
- try {
- cstmt = con.prepareCall(sql.toString());
- cstmt.setInt(1, procedureParameterOne.getType());
- cstmt.setString(2, procedureParameterTwo);
- cstmt.setString(3, "dbo."+checkTemplateNo);
- cstmt.execute();
- rs = cstmt.getResultSet();
- StringBuffer expressMessage = new StringBuffer("");
- StringBuffer instanceID = new StringBuffer("");
- while (rs.next()) {
- expressMessage.append("'").append(rs.getString("expressID")).append("',");
- instanceID.append("'").append(rs.getString("instanceID")).append("',");
- }
- returnObject.setExpressMessage(expressMessage.toString());
- returnObject.setInstanceID(instanceID.toString());
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- try {
- cstmt.close();
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- colseResultSet(rs);
- }
- return returnObject;
- }