Returning java resultset from MSSQL stored Procedure

See if this example helps you using CallableStatement:
http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html?page=2

API:
http://java.sun.com/javase/6/docs/api/java/sql/CallableStatement.html

The example I posted was Oracle, but is same concept on the Java side.  To execute the stored procedure, though, in MS SQL you will need to use EXEC verb instead of CALL.

CallableStatement cstmt = dbConn.prepareCall("{exec stored_procedure_name()}");

See

http://www.exampledepot.com/egs/java.sql/CallProcedure.html

if your sp returns multiple resultset,

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/tjvjdmlt.htm

I am writting

CallableStatement cstmt = conn.prepareCall("{exec sp_NumberOfNewUsers_PB}");

ResultSet rs = cstmt.getResultSet();

I am getting ResultSet as null value
???

Any help

Thanks

Have you called execute on the CS?

Sorry I didnot understood CS??

I called my SP as
CallableStatement cstmt = conn.prepareCall("{exec sp_NumberOfNewUsers_PB}");

ResultSet rs = cstmt.getResultSet();

My SP is
Create procedure sp_NumberOfNewUsers_PB as
SELECT
            CLIENTTYPE,WEBSITE,COUNT(*) CT
      FROM
            RET_CLIENTS
      WHERE
            CLIENTTYPE IN ('RETAIL', 'INSTITUTIONAL')       AND
            YEAR(CREATIONDATE) = YEAR(GETDATE())

      GROUP BY
            CLIENTTYPE, WEBSITE
order by WEBSITE, CLIENTTYPE

>>Sorry I didnot understood CS??

CS = CallableStatement. Looks like you didn't

CallableStatement cstmt = conn.prepareCall("{exec sp_NumberOfNewUsers_PB}");
'execute callable statement here
cstmt.execute();
ResultSet rs = cstmt.getResultSet();

Just like in example I posted link to earlier.  Think CEHJ's had same thing as well.

>>Looks like you didn't
?? Sorry again

Can you please help me in fixing
Connection conn = DBConnection.getConnection();
        try
        {
Connection conn = DBConnection.getConnection();
 CallableStatement cstmt = conn.prepareCall("{exec sp_NumberOfNewUsers_PB}");
            ResultSet rs = cstmt.getResultSet();
while (rs.next())
             {
}
}catch(Exception e)
        {
            throw e;
        }finally
        {
              conn.close();
        }

Connection conn = DBConnection.getConnection();
try
{
      Connection conn = DBConnection.getConnection();
      CallableStatement cstmt = conn.prepareCall("{exec sp_NumberOfNewUsers_PB}");
        ResultSet rs = cstmt.getResultSet();

      while (rs.next())
        {
      }
}catch(Exception e)
{
     throw e;
}finally
{
       conn.close();
}

See http:#23661681

See two previous posts for how to fix.  CEHJ's is nice as you can then use the boolean flag later in code.

Many Thanks. This works great