使用java从sql server存储过程中检索返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20268187/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieve the returned value from sql server stored procedure using java
提问by Muhammad Ijaz
Here is my store procedure that returns a value. I need to call this procedure and get that return value into my java program.
这是我的存储过程，它返回一个值。我需要调用此过程并将该返回值放入我的 java 程序中。
CREATE PROCEDURE my_procedure @advisor de , @adv_xml xml
AS
begin
declare
@psrg_idi idi,
@adv_cd cd,
@CurrDate cdt
set @adv_cd = (select adv_cd from dbo.ADVICE_LK where upper(rtrim(adv_de)) = upper(@advisor))
set @psrg_idi = 0
set @CurrDate = getdate()
BEGIN TRY
exec my_proc_2 @CurrDate,@psrg_idi output
insert into
ADVICE
(psrg_idi,
adv_cd,
psra_original_xml)
values
(@psrg_idi,
@adv_cd,
@adv_xml)
select
@psrg_idi as psrg_idi
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
return -1
END CATCH
end
CREATE PROCEDURE my_procedure @advisor de , @adv_xml xml
AS
begin
declare
@psrg_idi idi,
@adv_cd cd,
@CurrDate cdt
set @adv_cd = (select adv_cd from dbo.ADVICE_LK where upper(rtrim(adv_de)) = upper(@advisor))
set @psrg_idi = 0
set @CurrDate = getdate()
BEGIN TRY
exec my_proc_2 @CurrDate,@psrg_idi output
insert into
ADVICE
(psrg_idi,
adv_cd,
psra_original_xml)
values
(@psrg_idi,
@adv_cd,
@adv_xml)
select
@psrg_idi as psrg_idi
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
return -1
END CATCH
end
Here is how I am trying to get this value into my java program returned by the above stored procedure. When I call this procedure from java, all the expected values are stored into database tables. But I am receiving the returned value groupId as '0'.. any idea or help will be highly appreciated.
这是我如何尝试将此值放入上述存储过程返回的 java 程序中。当我从 java 调用此过程时，所有预期值都存储到数据库表中。但我收到返回值 groupId 为“0”.. 任何想法或帮助将不胜感激。
CallableStatement cs = con.prepareCall("{? = call my_procedure (?,?)}");
int i = 0;
cs.registerOutParameter(++i, java.sql.Types.INTEGER);
cs.setString(++i, advisor);
cs.setString(++i, adviceXml);
cs.execute();
int groupId = cs.getInt(1);
CallableStatement cs = con.prepareCall("{? = call my_procedure (?,?)}");
int i = 0;
cs.registerOutParameter(++i, java.sql.Types.INTEGER);
cs.setString(++i, advisor);
cs.setString(++i, adviceXml);
cs.execute();
int groupId = cs.getInt(1);
I have already reviewed the accepted answer https://stackoverflow.com/a/1948518/674476. I am also trying in the same way, but somehow not able to get returned value
我已经查看了已接受的答案https://stackoverflow.com/a/1948518/674476。我也在以同样的方式尝试，但不知何故无法获得返回值
采纳答案by Muhammad Ijaz
Below is the solution to the problem and it works perfectly fine in my case.
以下是问题的解决方案，在我的情况下它工作得很好。
CallableStatement cs = con.prepareCall("{call my_procedure(?,?)}");
int i = 0;
cs.setString(++i, advisor);
cs.setString(++i, adviceXml);
boolean isRs = cs.execute();
int updateCount = cs.getUpdateCount();
// cs.getUpdateCount() will return -1 if the current result is a ResultSet object
// or there are no more results
// cs.getMoreResults() will return true if the next result is a ResultSet object;
// false if it is an update count or there are no more results
while (!isRs && (cs.getUpdateCount() != -1)) {
isRs = cs.getMoreResults();
}
if (isRs) {
ResultSet rs = cs.getResultSet();
try {
if (rs.next()) {
groupId = rs.getString("psrg_idi");
}
} finally {
rs.close();
}
}
回答by neutrino
See accepted answer here.
请参阅此处接受的答案。
First, you should decide if your SP will return a ResultSet or not. I mean, when you catch an error, you do RETURN -1. But, if all works fine, you perform a SELECT, which returns a ResultSet.
首先，您应该决定您的 SP 是否会返回 ResultSet。我的意思是，当您发现错误时，您会这样做RETURN -1。但是，如果一切正常，您执行 a SELECT，它返回一个 ResultSet。
Said that, if you choose to return a value, you have to do:
说，如果你选择返回一个值，你必须这样做：
CallableStatement cs = con.prepareCall("{? = call my_procedure (?,?)}");
int i = 0;
cs.registerOutParameter(++i, java.sql.Types.INTEGER);
cs.setString(++i, advisor);
cs.setString(++i, adviceXml);
cs.execute();
int groupId = cs.getInt(1);
But, if you return a ResultSet, you have to do:
但是，如果您返回 ResultSet，则必须执行以下操作：
CallableStatement cs = con.prepareCall("{call my_procedure (?,?)}");
int i = 0;
cs.setString(++i, advisor);
cs.setString(++i, adviceXml);
ResultSet rs = cs.executeQuery();
if (rs.next())
int groupId = rs.getInt(1);
I've tested both methods using a toy SP:
我已经使用玩具 SP 测试了这两种方法：
CREATE PROCEDURE my_procedure @v1 int, @v2 int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--SELECT @v1 + @v2 as sumVal -- Comment this and uncomment previous line to test Return
RETURN @v1+@v2 -- Comment this and uncomment previous line to test ResultSet
END
