带有返回值的存储过程

本文介绍了带有返回值的存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个存储过程test，它看起来像：

I have a stored procedure "test", which looks like:

CREATE PROCEDURE test @name varchar(32) AS DECLARE @login_status TINYINT, @syb_login varchar(20), @syb_pass varchar(20) ... .. BEGIN SELECT @status = 0 SELECT @login as login, @pass as pass, @status as status RETURN 0 END

我需要传递一个输入参数myName作为此过程的输入参数，然后返回登录，传递和状态作为输出（仅来自一个记录）参数。

I need to pass a single input parameter "myName" as input parameter to this procedure and which in turn returns the login, pass and status as output (from only one record) parameters.

在JDBC中，我试着这样做：

In JDBC, I tried to do like below:

String query = "{call test(?,?,?)}"; System.out.println(query); CallableStatement proc = null; ResultSet rs; try { proc = connection.prepareCall(query); proc.setString(1, "myName"); proc.registerOutParameter(2, java.sql.Types.VARCHAR); proc.registerOutParameter(3, java.sql.Types.VARCHAR); proc.execute(); System.out.println(proc.getString(2));

这总是给出例外：

java.sql.SQLException: JZ0SG: A CallableStatement did not return as many output parameters as the application had registered for it. at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(Unknown Source) at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(Unknown Source) at com.sybase.jdbc4.jdbc.ParamManager.nextResult(Unknown Source) at com.sybase.jdbc4.jdbc.ParamManager.doGetOutValueAt(Unknown Source) at com.sybase.jdbc4.jdbc.ParamManager.getOutValueAt(Unknown Source) at com.sybase.jdbc4.jdbc.SybCallableStatement.getString(Unknown Source)

I尝试使用 JDBC执行SQL Server带有返回值和输入/输出参数的存储过程， msdn.microsoft/en-us/library/ms378108.aspx 但这不起作用。

I tried with JDBC execute SQL Server stored procedure with return value and input/output parameters, msdn.microsoft/en-us/library/ms378108.aspx but this didn't work.

推荐答案

有两种方法：

1。重新定义您的程序（推荐）

创建存储过程时必须声明输出参数：

Output paramteres have to be declared when creating a stored procedure:

CREATE PROCEDURE test @name varchar(32), @login varchar(32) = null output, @pass varchar(32) = null output, @status int = -1 output AS BEGIN SET @status = 0 SELECT @login = logincolumn, @pass = passcolumn FROM usertable END

然后你可以使用它使用JDBC如下：

Then you can use it with JDBC as follows:

String query = "{call test(?,?,?,?)}"; [...] proc = connection.prepareCall(query); proc.setString(1, "myName"); proc.registerOutParameter(2, java.sql.Types.VARCHAR); proc.registerOutParameter(3, java.sql.Types.VARCHAR); proc.registerOutParameter(4, java.sql.Types.INTEGER);

注意：

• 您必须设置或选择到声明的变量中，为它们输出值。
• 通常传递密码不是一个好主意

• You have to set or select into the declared variables to give them output values.
• Usually it isn't a good idea to pass passwords around

2 。从多个结果集中捕获结果（不推荐）

Connection con; CallableStatement proc = null; ResultSet rs; String qry = "{call test(?)}"; proc = con.prepareCall(qry); proc.setString(1, "name"); proc.executeQuery(); // first result set returning the status rs = proc.getResultSet(); if (rs.next()) { System.out.println(rs.getString(1)); } // second result set returning login and pass if (proc.getMoreResults()) { rs = proc.getResultSet(); if (rs.next()) { System.out.println(rs.getString("login")); System.out.println(rs.getString("pass")); } }