This discussion is archived
4 Replies Latest reply: Oct 12, 2012 7:59 PM by rp0428 RSS

How to call a sp in Sql server which has no Out parameter

962769 Newbie
Currently Being Moderated
Hi all,

I am trying to call a stored procedure in Sql Server through jdbc. The Stored Procedure has no out parameter in it. It has a select query at the end which retrieves some rows when

we execute the procedure. When I execute the procedure in the sql server I am able to see the output rows but when I am trying to invoke the SP I am getting an exception

pasted below:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
     at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:408)
     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
     at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
     at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
     at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
     at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
     at sqlserverspcall.SqlServerSPCall.main(SqlServerSPCall.java:50)

Could it be because of not having Out parameter in the procedure. I guess the ResultSet comes only if the procedure has an Out parameter. Is there a way to get the output from

this kind of procedures. Please help me.

Thanks In Advance
  • 1. Re: How to call a sp in Sql server which has no Out parameter
    rp0428 Guru
    Currently Being Moderated
    >
    Could it be because of not having Out parameter in the procedure. I guess the ResultSet comes only if the procedure has an Out parameter. Is there a way to get the output from

    this kind of procedures.
    >
    You aren't going to get much help if you don't post the code you are using to call the stored procedure.

    Are you using the executeQuery method to execute the procedure?
  • 2. Re: How to call a sp in Sql server which has no Out parameter
    Joe Weinstein Expert
    Currently Being Moderated
    Your problem is that the procedure returns something(s) else before the result set you want. These could
    be (likely) update counts related to inserts/updates/deletes your procedure did. You must absorb all returns
    in order, to get to the result set.
    Here is the canonical code style for processing all the returns from an arbitrary procedure. It uses the
    execute() call:

    boolean getResultSetNow = ps.execute();
    int updateCount = -1;

    while (true) { // handle all in-line results from any procedure
    if (getResultSetNow) {
    ResultSet r = ps.getResultSet();
    while (r.next()) {
    // fully process result set before calling getMoreResults() again!
    }
    r.close();
    } else {
    updateCount = ps.getUpdateCount();
    if (updateCount != -1) { // it's a valid update count
    if (you want) System.out.println("Processing an update count of " + updateCount);
    }
    }
    if ((!getResultSetNow) && (updateCount == -1)) break; // done with loop
    getResultSetNow = ps.getMoreResults();
    }
    // if there are output parameters get them now after the loop
  • 3. Re: How to call a sp in Sql server which has no Out parameter
    962769 Newbie
    Currently Being Moderated
    Hi rp0428,

    This is the code I am using for calling the proc.

    public class SqlServerSPCall {
    final static String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    final static String connectionURL = "jdbc:sqlserver://servername:port";
    final static String userID = "userid";
    final static String userPassword = "password";
    static Connection con = null;
    public SqlServerSPCall() {
    try
    {

    System.out.print(" Loading JDBC Driver -> " + driverClass + "\n");
    Class.forName(driverClass).newInstance();

    System.out.print(" Connecting to -> " + connectionURL + "\n");
    this.con = DriverManager.getConnection(connectionURL, userID, userPassword);
    System.out.print(" Connected as -> " + userID + "\n\n");

    }
    catch (ClassNotFoundException e)
    {
    e.printStackTrace();
    } catch (InstantiationException e)
    {
    e.printStackTrace();
    } catch (IllegalAccessException e)
    {
    e.printStackTrace();
    } catch (SQLException e)
    {
    e.printStackTrace();
    }
    }
    public static void main(String ...a) throws Exception{
    SqlServerSPCall spcall = new SqlServerSPCall();
    String SPsql = "Exec sp_demopric ?,?,?";
    PreparedStatement ps = con.prepareStatement(SPsql);
    ps.setEscapeProcessing(true);
    ps.setString(1, "' NewDataSet'");
    ps.setString(2, "'second param'");
    ps.setInt(3, 0);
    ResultSet rs = ps.executeQuery();

    while(rs.next()){
    System.out.println(rs.getString(1));
    }
    }

    I am not getting any result set for the procedure...
  • 4. Re: How to call a sp in Sql server which has no Out parameter
    rp0428 Guru
    Currently Being Moderated
    The only examples I found use SQLServerPreparedStatement. Have you tried that?

    Also, you are using 'exec'
    String SPsql = "Exec sp_demopric ?,?,?"; 
    PreparedStatement ps = con.prepareStatement(SPsql);
    ps.setEscapeProcessing(true);
    ps.setString(1, "' NewDataSet'");
    Are you sure 'exec' is what you should be using? And are you sure your procedure isn't generating an exception and then swallowing it in a generic exception handler?

    I found this that might help
    >
    Looking at your stored procedure, though, there are three results that you need to process: one update count for each INSERT, and a result set for the SELECT. In order to process all of the results from a statement that produces multiple results, you need to use Statement.execute() (not executeQuery()) in conjunction with Statement.getMoreResults(), Statement.getResultSet() and Statement.getUpdateCount(). You should only use Statement.executeQuery() with statements that produce a single ResultSet as their only result.
    >

    But maybe you should try the method Joe suggested. Is your procedure doing other things that are generating results? Like updates or inserts that will generate a count that you need to ignore? Joe's method will work in those cases.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points