I am trying to call a simple stored procedure (MS SQLServer) that returns a list of event records and a return code. The code for the stored procedure is pretty straight forward:

if object_id('dbo.selectAllEvents') is null
BEGIN
EXECUTE('create procedure dbo.selectAllEvents as return 0')
END
GO

ALTER PROCEDURE dbo.selectAllEvents AS
BEGIN
SET NOCOUNT ON
-- Parameter validation could go here. return error on validation failure

-- Typical processing/fetch of data
SELECT EventID
, EventDate
, Title
FROM Event

-- Return code. Typically zero on success
RETURN -1
END

If I execute the SProc w/o trying to access the return code, it works fine. Although this application is completely contrived, I am using to figure out how to access SProcs in the simple case and then apply the knowledge to more complex problems.
I have two questions: Is it possible to access the return value from a stored procedure? And, if so, how to I access the value?
I made the assumption that the return code is the zeroth parameter, but apparently this is a bad assumption.

Hibernate version: 3.1.3

Mapping documents:
<hibernate-mapping>

<class name="events.Event" table="Event">
<id name="id" column="EventID">
<generator class="native"/>
</id>
<property name="date" type="timestamp" column="EventDate"/>
<property name="title" column="Title"/>

<set name="participants" table="PersonEvent" inverse="true">
<key column="EventID"/>
<many-to-many column="PersonID" class="events.Person"/>
</set>
</class>

<sql-query name="selectAllEvents_SP" callable="true">
<return alias="events" class="events.Event">
<return-property name="id" column="EventID"/>
<return-property name="date" column="EventDate"/>
<return-property name="title" column="Title"/>
</return>

{ ? = call selectAllEvents() }
</sql-query>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
session.beginTransaction();

List result = null;
try {
int retcode = 0;
result = session.getNamedQuery("selectAllEvents_SP")
.setParameter(0,retcode)
.list();
}catch (Exception ex){
ex.printStackTrace();
}

session.getTransaction().commit();
return result;

Full stack trace of any exception that occurs:
java.lang.IndexOutOfBoundsException: Remember that ordinal parameters are 1-based!
at org.hibernate.engine.query.ParameterMetadata.getOrdinalParameterDescriptor(ParameterMetadata.java:55)
at org.hibernate.engine.query.ParameterMetadata.getOrdinalParameterExpectedType(ParameterMetadata.java:61)
at org.hibernate.impl.AbstractQueryImpl.determineType(AbstractQueryImpl.java:382)
at org.hibernate.impl.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:362)
at events.EventManager.listEvents(EventManager.java:64)
at events.EventManager.main(EventManager.java:101)

Name and version of the database you are using:
Microsoft SQL Server 2005

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Hi,

In my opinion you cannot ask for a return code AND a recordset.

I used a <return-scalar> tag to deal with a single return code. In your stored procedure you have to make a select statement, like SELECT "-1"

I hope this helps.

Regards,
Martijn

use named parameter instead: