JDBC使用返回值和输入/输出参数执行SQL Server存储过程

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

在互联网上我看到了使用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:

然后我决定做SQL Management Studio生成的SQL代码:

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 '='

它在SQL Management Studio上也不起作用因为'OUT' appers before '='

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

这让我毫无头绪，因为这两种方法都行不通。

Any suggestions?

有什么建议吗?

Thank you!

谢谢你！

2 个解决方案

#1

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.

因此，第一个示例在JDBC调用-escape的解析器中触发一个异常，因为您将调用放在返回值之前。我不太清楚第二个问题。

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:

您可以在MSDN上找到关于JDBC驱动程序的更多信息:

Using Statements with Stored Procedures
使用带有存储过程的语句
Using a Stored Procedure with a Return Status
使用返回状态的存储过程
Using a Stored Procedure with Output Parameters
使用带有输出参数的存储过程

#2

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

而且，对于没有参数的存储过程，它看起来就像……

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

#1