File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes SQL Server 2000 Return Value Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Server 2000 Return Value" Watch "SQL Server 2000 Return Value" New topic

SQL Server 2000 Return Value

Serena Zhou
Ranch Hand

Joined: Dec 13, 2003
Posts: 31
I used a stored procedure to add a record to the SQL Server 2000 DB, In my stored procedure I want to return the ID if the record has been successfully added,
statement = con.prepareCall("{?=call spAddEmployee(?, ?)}");
statement.registerOutParameter(1, Types.INTEGER);
statement.setString(1, ...);
int result = statmemt.executeQuery();

My spAddEMployee:

CREATE PROCEDURE dbo.spAddEmployee(
@ID char(20),
@Name, char(30)
AS INSERT INTO VendorInventory (ID, Name)
VALUES(@ID, @Name)
Return @ID
But from the tutorial, I found that the callable statement will return the number of rows been added(or updated), is there anyway I can return the value I specified in the stored procedure?

Edwin Keeton
Ranch Hand

Joined: Jul 10, 2002
Posts: 214

The stored procedure must identify the out parameter with the keyword OUT.
Then you can read the parameter by name or by index with one of the getXXX methods of CallableStatement.

I agree. Here's the link:
subject: SQL Server 2000 Return Value
It's not a secret anymore!