JDBC使用返回值和输入/输出参数执行SQL Server存储过程 [英] JDBC execute SQL Server stored procedure with return value and input/output parameters
问题描述
我有一个存储过程，该过程使用1个输入值，2个输出参数并在执行时还返回一个值
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);
但这给了我错误
"Incorrect syntax near '{'"
然后，我决定像SQL Management Studio生成的SQL代码一样进行操作:
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);
但这给了我错误
"Incorrect syntax near '='"
我认为这是因为查询已转换为
I think this is because the query gets transformed to
"exec @P1 OUT = spName @P2, @P3 OUT, @P4 OUT"
，它在SQL Management Studio上也不起作用，因为"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.
有什么建议吗?
谢谢！
推荐答案
调用存储过程的语法是:
The syntax for calling a stored procedure is:
{[?=]call procedure-name[([parameter][,[parameter]]...)]}
因此，第一个示例在JDBC调用转义的解析器中触发异常，因为您将call放在了返回值之前.我不确定第二个问题.
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(?, ?, ?)}");
您可以在MSDN上找到专门针对Microsoft JDBC驱动程序的更多信息:
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
这篇关于JDBC使用返回值和输入/输出参数执行SQL Server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
