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 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}
Note
For more information about the SQL escape sequences, see Using SQL escape sequences.
As an example, create the following stored procedure in the AdventureWorks2022 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 10 contacts that are in the Person.Contact table.
In the following example, an open connection to the AdventureWorks2022 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) throws SQLException {
try(Statement stmt = con.createStatement();) {
ResultSet rs = stmt.executeQuery("{call dbo.GetContactFormalNames}");
while (rs.next()) {
System.out.println(rs.getString("FormalName"));
}
}
}