SQL Server JDBC - INOUT parameter SQLServer

Author

INOUT parameter SQLServer

jaya nair

2005-08-31, 8:23 pm

HI,

What is the equivalent for INOUT parameter in SQLServer.
In Oracle storedprocedure we can declare a variable as INOUT parameter.
Can we have a equivalent in SQLServer.
If so, how can i set value for that parameter in jdbc.

thanks in advance

Joe Weinstein

2005-08-31, 8:23 pm

jaya nair wrote:

> HI,
>
> What is the equivalent for INOUT parameter in SQLServer.
> In Oracle storedprocedure we can declare a variable as INOUT parameter.
> Can we have a equivalent in SQLServer.
> If so, how can i set value for that parameter in jdbc.
>
> thanks in advance

Here's an example:

Statement s = c.createStatement();

try { s.executeUpdate("drop procedure joeproc"); } catch (Exception ignore){}

s.executeUpdate("create procedure joeproc @foo int OUTPUT as "
+ " begin "
+ " select (convert(varchar, @foo)) "
+ " set @foo = @foo + @foo "
+ " end ");

CallableStatement ps = c.prepareCall("{ call joeproc(?) }");
ps.setInt(1, 100 );
ps. registerOutParameter
(1, java.sql.Types.INTEGER);
boolean getResultSet = ps.execute();
int updateCount = -1;

while (true) { // handle all in-line results from any procedure
if (getResultSet) {
ResultSet r = ps.getResultSet();
while (r.next()) {
System.out.println("We got a result set row with " + r.getString(1) );
}
r.close();
} else {
updateCount = ps.getUpdateCount();
if (updateCount != -1) {
;// process update count
}
}
if ((!getResultSet) && (updateCount == -1)) break; // done with loop
getResultSet = ps.getMoreResults();
}
System.out.println("We got an output parameter value " + ps.getInt(1) );

I get:
We got a result set row with 100
We got an output parameter value 200

Joe Weinstein at BEA Systems

Sponsored Links