How to Execute a Ms SQL Server Stored Procedure in Java/Jsp, Returning Table Data

How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?

Our server calls stored procs from Java like so - works on both SQL Server 2000 & 2008:

String SPsql = "EXEC <sp_name> ?,?";   // for stored proc taking 2 parameters
Connection con = SmartPoolFactory.getConnection();   // java.sql.Connection
PreparedStatement ps = con.prepareStatement(SPsql);
ps.setEscapeProcessing(true);
ps.setQueryTimeout(<timeout value>);
ps.setString(1, <param1>);
ps.setString(2, <param2>);
ResultSet rs = ps.executeQuery();

how to execute a storedprocedure from preparestatement

Your stored procedure is expecting two arguments which you will need to supply to the prepared statement. Your SQL needs placeholders for the arguments and the arguments need to be set on the PreparedStatement object. Your code would then look something like:

String itemshelf = DBshelf;// edittext for putting in values on set
String itemcard = DBcard;// edittext for putting in values on where
PreparedStatement preparedStatement = con.prepareStatement("EXEC [dbo].[spUpd_Location] ?, ?;");
preparedStatement.setString(1, itemshelf);
preparedStatement.setString(2, itemcard);
preparedStatement.executeUpdate();

java code make a stored procedure return a resultset in DAO layer

SQL Server knows two types of procedures returning results:

Batches

The procedure looks something like this:

CREATE PROCEDURE p_results(
  @p_result_sets INT
)
AS
BEGIN
  IF @p_result_sets = 1 BEGIN
    SELECT 1 a;
  END
  ELSE IF @p_result_sets = 2 BEGIN
    SELECT 1 a;
    SELECT 1 b UNION SELECT 2 b;
  END
END;

In this case, you don't know in advance what the result sets will look like, and how many of them you'll get. You will have to run the procedure using Statement.execute() as follows:

try (CallableStatement stmt = con.prepareCall("...")) {
    boolean results = stmt.execute();

    for (;;) {
        if (results)
            try (ResultSet rs = stmt.getResultSet()) {
                // ... Fetch your results here
            }
        else if (stmt.getUpdateCount() != -1) {}
        else
            break;

        results = stmt.getMoreResults();
    }

    // After all results are fetched, you can also retrieve OUT parameters, if applicable
}

Table-valued functions

The function looks something like this:

CREATE FUNCTION f_tables1 ()
RETURNS @out_table TABLE (
  column_value INTEGER
)
AS
BEGIN
  INSERT @out_table
  VALUES (1)
  RETURN
END

In this case, you don't really need a CallableStatement. An ordinary SELECT statement will do:

try (PreparedStatement stmt = con.prepareStatement("SELECT * FROM f_tables1()");
     ResultSet rs = stmt.executeQuery()) {
    // ... Fetch your results here
}

---

Related Topics