A stored procedure is a database procedure, similar to a procedure in other programming languages, which is contained within the database itself. In SQL Server, stored procedures can be created by using Transact-SQL, or by using the common language runtime (CLR) and one of the Visual Studio 2005 programming languages such as Visual Basic or C#. Generally, SQL Server stored procedures can do the following:

• Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
  
  
• Contain programming statements that perform operations in the database, including calling other procedures.
  
  
• Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).
  
  

To work with data in a SQL Server database by using a stored procedure, the Microsoft SQL Server 2005 JDBC Driver provides the SQLServerStatement, SQLServerPreparedStatement, and SQLServerCallableStatement classes. Which class you use depends on whether IN (input) or OUT (output) parameters are required by the stored procedure. If the stored procedure requires no IN or OUT parameters, you can use the SQLServerStatement class; if the stored procedure will be called multiple times, or requires only IN parameters, you can use the SQLServerPreparedStatement class. If the stored procedure requires both IN and OUT parameters, you should use the SQLServerCallableStatement class. It is only when the stored procedure requires OUT parameters that you will need the overhead of using the SQLServerCallableStatement class.

When you use the JDBC driver to call a stored procedure with parameters, you must use the call SQL escape sequence together with the prepareCall method of the SQLServerConnection class. The complete syntax for the call escape sequence is as follows:

{[?=]call procedure-name[([parameter][,[parameter]]...)]}

The topics in this section describe the ways that you can call SQL Server stored procedures by using the JDBC driver and the call SQL escape sequence.