CODEWITHSUNDEEP
sql-serverstored-proceduresjdbc
user2064433
user2064433

Reputation: 23

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

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!

Upvotes: 2

Views: 15824

Answers (2)

JoeManiaci
JoeManiaci

Reputation: 475

Also, for stored procedures without any arguments, it would just look like...

this.connection.prepareCall("{call dbo.storedProcedure()}")

Upvotes: 0

Mark Rotteveel
Mark Rotteveel

Reputation: 109257

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:

Upvotes: 4

Related Questions