What I am trying to do is :

I have inline sql in the java code,so we want to move these sql to store proc for better performance.

Instead of inline sql we will call store proc but problem is I am not sure how I can retrieve data from the storeproc, right now it is executing the sql and returning the result set.

So please let me know if we convert below sql to store proc and call the storeproc , how we can retrieve the resultset? I am not sure if it is possible.

Please advice, urgent.


public ArrayList getCompleteArchivedReconciledReport(String username, int database, Date afterDate, Date toDate, int msPracticeId) throws SQLException {
if(msPracticeId <= 0) {
throw new SQLException("Cannot find information due to not having MsPracticeId in getReconciledGroupcast()"+Statics.EMAIL_ERROR_MESS AGE);
}

String sql3 ="";

ArrayList alist = new ArrayList();
Connection conn = null;
PreparedStatement pStmt = null;
ResultSet rs = null;

try {
conn = this.getDBConnection();

sql3 = "Select "
+ " d.archive_id, d.[trkID] as theID, d.[ctrlgrp], d.[chgno], p2.msdatabaseid, d.[amt], "
+ " d.[gpmsdate], d.[depositDate], pp.[provgrpcode] 'doctordep', d.[poster], "
+ " d.[poscode], d.[batchNum], d.[prac] as practice, d.[batchTotal], "
+ " d.[totalPosted], d.[total], d.[logPoster], d.[logDate], "
+ " p2.[receivedDate], pp.[postDate], d.[acctno], p2.[batchType], p2.comments as comments "
+ " FROM "
+ " archive_reconcile_detail as d with (NOLOCK) "
+ " join archive_payment_t pp with (NOLOCK)"
+ " on pp.archive_id = d.archive_id and pp.chgno = d.chgno "
+ " join archive_manager as m with (NOLOCK) on m.id = d.archive_id and m.usable_item = 'Y' "
+ " join archive_posting as p2 with (NOLOCK) on d.trkId = p2.posting_id and p2.archive_id = d.archive_id "
+ " join ms_practice as p with (NOLOCK) on p.msdatabaseid = p2.msdatabaseid "
+ " where d.depositDate between ? and ? and p2.msdatabaseid = ? and p.id = ? "
+ " and m.date_created >= ALL (select m1.date_created from archive_manager as m1 with (NOLOCK) where m1.original_posting_id = m.original_posting_id)"
+ " order by d.depositDate, d.trkID, d.poster ";
//log.debug("getCompleteArchivedReconciledReport: " + sql3);
pStmt = conn.prepareStatement(sql3);
pStmt.setDate(1, afterDate);
pStmt.setDate(2, toDate);
pStmt.setInt(3, database);
pStmt.setInt(4, msPracticeId);
rs = pStmt.executeQuery();
while (rs.next()) {
ReconGPMS_complete re = this.createReconGPMS_complete(rs);
OpsManager.addArchiveIdToReconGPMS_complete(re);
alist.add(re);
}
} catch (SQLException ex) {
ex.printStackTrace();
throw ex;
} finally {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pStmt != null) {
try {
pStmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return alist;
}