Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL Server 2000 Return Value

 
Serena Zhou
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
)
declare
AS INSERT INTO VendorInventory (ID, Name)
VALUES(@ID, @Name)
Return @ID
GO
**********************************************************/
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?

Thanks!
 
Edwin Keeton
Ranch Hand
Posts: 214
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!