title: sql server stored proc, can't get result set back in java
body: <p>I have created a stored proc in a sql server db that appears to be executing  properly for one record. The code is shown below:</p>
  
  <pre><code>try {
                  String className="com.microsoft.sqlserver.jdbc.SQLServerDriver";
                  String username="username";
                  String url="jdbc:sqlserver://someDB:8808;SelectMethod=cursor";
                  String password="password";
                  Class.forName(className);
                  conn = DriverManager.getConnection(url, username, password);
  
  
  
  
                  // need to obtain store procedure in db(a sql script stored in our sql server db)
                  CallableStatement cstmt = conn.prepareCall("{call dbo.sp_SomeProc(?,?,?,?,?,?,?,?,?)}");
                  cstmt.setString (1,"id34234, id34246, id234234");
                  cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
                  cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);
                  cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
                  cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
                  cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
                  cstmt.registerOutParameter(7, java.sql.Types.VARCHAR);
                  cstmt.registerOutParameter(8, java.sql.Types.VARCHAR);
                  cstmt.registerOutParameter(9, java.sql.Types.VARCHAR);
  
  
  
  
                 int rowsAffected = 0;
                 boolean isResults = cstmt.execute();
                 while(isResults  || rowsAffected != -1) {
                       if(isResults) {
                           rs = cstmt.getResultSet();
                           break;
                       }else {
                            rowsAffected = cstmt.getUpdateCount();
                       }
                       isResults = cstmt.getMoreResults();
                 }
              while(rs.next()) {
                 slInfo.add(cstmt.getString(2));
                 slInfo.add(cstmt.getString(3));
                 slInfo.add(cstmt.getString(4));
                 slInfo.add(cstmt.getString(5));
                 slInfo.add(cstmt.getString(6));
                 slInfo.add(cstmt.getString(7));
                 slInfo.add(cstmt.getString(8));
                 slInfo.add(cstmt.getString(9));
               }
  
  
                  writeToFile(outFileName, slInfo);
  
              }catch(SQLException ex) {
                  ex.printStackTrace();
  
              }catch(ClassNotFoundException ex) {
                  ex.printStackTrace();
              }finally {
                  try { cstmt.close(); } catch (Exception e) { /* ignored */ }
                  try { conn.close(); } catch (Exception e) { /* ignored */ }
              }
  </code></pre>
  
  <p>The issue is since I have more than one record, I need to obtain a result set. However, every attempt other than <code>executeUpdate()</code> results in an exception.</p>
  
  <p>My first attempt: </p>
  
  <p><code>ResultSet rs = cstmt.executeQuery() // instead cstmt.executeUpdate()</code><br>
  the exception:<br>
  <code>com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)</code></p>
  
  <p>Second attempt: </p>
  
  <pre><code> cstmt.execute();
   ResultSet rs = (ResultSet)cstmt.getObject(1) // not actually sure what index to use here
  </code></pre>
  
  <p>It appears that the first attempt is what I have seen other people do, but I can't figure out what I am doing wrong. Any help would be most appreciated.</p>
  
  <p><strong>UPDATE</strong><br>
  Here is the stored proc:</p>
  
  <pre><code>USE [someDB]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[sp_GetAvatarInfo] 
  (
  @idList VARCHAR(MAX), 
  @uniqueIdent VARCHAR(64) OUTPUT,
  @FULLNAME VARCHAR(128) OUTPUT, 
  @CCNUMSEQ VARCHAR(64) OUTPUT, 
  @ALIASTYPE VARCHAR(64) OUTPUT,
  @SLNUMBER VARCHAR(64) OUTPUT,
  @TISSUETYPE VARCHAR(64) OUTPUT,
  @SAMPLESUBTYPE VARCHAR(64) OUTPUT,
  @SUBMITTEDDIAG VARCHAR(64) OUTPUT
  )
  AS 
  BEGIN 
  
  DECLARE @idAvatarEvent INT,
          @avatarDisease INT;
  
  SELECT @idAvatarEvent = idEventType
          FROM CCR.dbo.EventType
  WHERE codeCancerGroup = 'TCC' AND eventType = 'Avatar';
  
  --Create table to hold all SL ids that are involved in the search
  IF (OBJECT_ID('tempdb.dbo.#SLList') IS NOT NULL) DROP TABLE #SLList;
  CREATE TABLE #SLList
  (
          idAlias INT NOT NULL IDENTITY (1,1)
          ,SLNumber VARCHar(100)
  )
  
  
  INSERT INTO #SLList (SLNumber)
  SELECT [str]
  FROM BST.dbo.fn_parseString(@idList, ',');
  
  SELECT @avatarDisease = ATT.idAttribute
          FROM CCR.dbo.Attribute          ATT
            JOIN CCR.dbo.AttributeContainer ATC ON ATT.idAttributeContainer = ATC.idAttributeContainer
  WHERE ATT.attributeName = 'avatarDiseaseGroup'
          AND ATC.containerName = 'Avatar'
  
    SELECT @PERMRN = per.mrn,
           @FULLNAME = per.firstName + ' ' + per.lastName,
           @CCNUMSEQ = ali.ccNumberSeq, 
           @ALIASTYPE = sat.aliasType, 
           @SLNUMBER = sl.SLNumber, 
           @TISSUETYPE = sst.description,
           @SAMPLESUBTYPE = case when pt.preparationType LIKE '%Formalin%' THEN 'FFPE' ELSE pt.preparationType END,
           @SUBMITTEDDIAG = ac.choice
          FROM #SLList sl
                  LEFT JOIN DB1.dbo.AliquotAlias sa on sl.SLNumber = sa.alias
                  LEFT JOIN DB1.dbo.AliquotAliasType sat on sa.idAliasType = sat.idAliasType
                  LEFT JOIN DB1.dbo.VIEW_Aliquot2 ali on sa.idAliquot = ali.idAliquot
                  LEFT JOIN DB1.dbo.Sample sam on ali.idSample = sam.id
                  LEFT JOIN DB1.dbo.VIEW_SampleSubType sst on sam.codeSampleSubType = sst.codeSampleSubType
                  LEFT JOIN DB1.dbo.PreparationType pt on sam.idPreparationType = pt.id
                  LEFT JOIN DB1.dbo.BSTCollection col on sam.idBSTCollection = col.idBSTCollection
                  LEFT JOIN DB1.dbo.BSTPatient pat on col.idBSTPatient = pat.idBSTPatient
                  LEFT JOIN DB2.dbo.Person per on pat.idPerson = per.idPerson
                  LEFT JOIN DB3.dbo.Patient cpat on per.idPerson = cpat.idPerson
                  LEFT JOIN DB3.dbo.PatientCancerGroup pcg on cpat.idPatient = pcg.idPatient AND pcg.codeCancerGroup = 'TCC'
                  LEFT JOIN DB3.dbo.MedicalEvent med on pcg.idPatient = med.idPatient AND pcg.codeCancerGroup = med.codeCancerGroup AND med.idEventType = @idAvatarEvent
                  LEFT JOIN DB3.dbo.AttributeValue av on med.idAttributeValueSet = av.idAttributeValueSet AND av.idAttribute = @avatarDisease
                  LEFT JOIN DB3.dbo.AttributeChoice ac on av.valueIdAttributeChoice = ac.idAttributeChoice
          ORDER BY MRN
  END;
  </code></pre>