Using a Stored Procedure with No Parameters

The simplest kind of SQL Server stored procedure that you can call is one that contains no parameters and returns a single result set. The Microsoft SQL Server JDBC Driver provides the SQLServerStatement class, which you can use to call this kind of stored procedure and process the data that it returns.

When you use the JDBC driver to call a stored procedure without parameters, you must use thecallSQL escape sequence. The syntax for thecallescape sequence with no parameters is as follows:

{call procedure-name}

As an example, create the following stored procedure in the SQL Server 2005 AdventureWorks sample database:

CREATE PROCEDURE GetContactFormalNames 
AS
BEGIN
   SELECT TOP 10 Title + ' ' + FirstName + ' ' + LastName AS FormalName 
   FROM Person.Contact
END

This stored procedure returns a single result set that contains one column of data, which is a combination of the title, first name, and last name of the top ten contacts that are in the Person.Contact table.

In the following example, an open connection to the AdventureWorks sample database is passed in to the function, and the executeQuery method is used to call the GetContactFormalNames stored procedure.

public static void executeSprocNoParams(Connection con) {
   try {
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("{call dbo.GetContactFormalNames}");

      while (rs.next()) {
         System.out.println(rs.getString("FormalName"));
      }
      rs.close();
      stmt.close();
   }
   catch (Exception e) {
      e.printStackTrace();
   }
}

See Also

Other Resources

Using Statements with Stored Procedures