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
4
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
0
Also, for stored procedures without any arguments, it would just look like...
而且,对于没有参数的存储过程,它看起来就像……
this.connection.prepareCall("{call dbo.storedProcedure()}")
#1
4
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
0
Also, for stored procedures without any arguments, it would just look like...
而且,对于没有参数的存储过程,它看起来就像……
this.connection.prepareCall("{call dbo.storedProcedure()}")