Using a Stored Procedure with No Parameters

 

DownloadDownload JDBC Driver

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 JDBC Driver for SQL Server 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 the call SQL escape sequence. The syntax for the call escape sequence with no parameters is as follows:

{call procedure-name}

System_CAPS_ICON_note.jpg Note


For more information about the SQL escape sequences, see Using SQL Escape Sequences.

As an example, create the following stored procedure in the 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();  
   }  
}  

Using Statements with Stored Procedures

Community Additions

ADD
Show: