JDBC execute SQL Server stored procedure with return value and input/output parameters

Question

I have a stored procedure that takes 1 input value, 2 output parameters and also returns a value on execution

Over the internet i saw references using Call

CallableStatement cstmt = conn.prepareCall("{call ? = spName(?, ?, ?)}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setObject(2, Types.INTEGER);
cstmt.registerOutParameter(3, Types.NVARCHAR);
cstmt.registerOutParameter(4, Types.NVARCHAR);

But this gives me the error

"Incorrect syntax near '{'"

Then i decided to do like the SQL Management Studio generated SQL code:

CallableStatement cstmt = conn.prepareCall("exec ? = spName ?, ?, ?");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setObject(2, Types.INTEGER);
cstmt.registerOutParameter(3, Types.NVARCHAR);
cstmt.registerOutParameter(4, Types.NVARCHAR);

But this gives me the error

"Incorrect syntax near '='"

I think this is because the query gets transformed to

"exec @P1 OUT = spName @P2, @P3 OUT, @P4 OUT" 

and it doesn't work either on SQL Management Studio because 'OUT' appers before '='

And this leaves me without ideas because it doesn't work either way.

Any suggestions?

Thank you!

Solution

The syntax for calling a stored procedure is:

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

So your first example triggers an exception in the parser of the JDBC call-escape because you put call before the return value. I am not sure about the problem with the second one.

So I would expect it to work if you modify your first example to:

CallableStatement cstmt = conn.prepareCall("{?=call spName(?, ?, ?)}");

You can find more information specifically for the Microsoft JDBC driver on MSDN:

• Using Statements with Stored Procedures
• Using a Stored Procedure with a Return Status
• Using a Stored Procedure with Output Parameters