Detailed description of stored procedures for calling SQL Server using Java-calling stored procedures with returned states

When a JDBC driver is used to call such a stored procedure, the call SQL escape sequence must be used in conjunction with the preparecall METHOD OF THE sqlserverconnection class. The syntax for returning the call escape sequence of the status parameter is as follows:

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

When constructing the call escape sequence, use? (Question mark) character to specify the return status parameter. This character acts as a placeholder for the parameter values returned from this stored procedure. To specify a value for the returned status parameter, you must use the registeroutparameter method of the sqlservercallablestatement class to specify the Data Type of the parameter before executing the stored procedure.

In addition, when passing the return status parameter value to the registeroutparameter method, you must not only specify the Data Type of the parameter to be used, but also specify the ordinal position of the parameter in the stored procedure. For the return status parameter, its ordinal position is always 1, because it is always the first parameter when a stored procedure is called. Although the sqlservercallablestatement class supports parameter names to indicate specific parameters, you can only use the serial number and position number of parameters for return status parameters.

As an instance, create the following stored procedure in the SQL Server 2005 adventureworks sample database:

SQL code:

Create procedure checkcontactcity

(@ Cityname char (50 ))

As

Begin

If (select count (*)

From person. Address

Where city = @ cityname)> 1)

Return 1

Else

Return 0

End

The stored procedure returns a status value of 1 or 0, depending on whether the city specified by cityname can be found in the table person. Address.

In the following example, the open connection of the adventureworks sample database will be passed to this function, and the checkcontactcity stored procedure will be called using the execute method:

Java codepublic static void executestoredprocedure (connection con ){

Try {

Callablestatement cstmt = con. preparecall ("{? = Call DBO. checkcontactcity (?)} ");

Cstmt. registeroutparameter (1, java. SQL. types. integer );

Cstmt. setstring (2, "Atlanta ");

Cstmt.exe cute ();

System. Out. println ("Return status:" + cstmt. getint (1 ));

}

Cstmt. Close ();

Catch (exception e ){

E. printstacktrace ();

}

}

From: http://www.cn-java.com/www1? Action-viewnews-itemid-55626