Recently I ran into a situation with NHibernate where I needed to call a stored procedure. Ok. That should be easy right? Well, not so much. I mean it wasn't rocket science, but it didn't feel like a first class citizen in the NHibernate framework, and maybe it's not supposed to be...I don't know.

So I googled it, of course, and I found a few related blog posts referencing the different ways of going about this. The problems started when my stored procedure needed to return multiple rows and not a scalar value. By problem I mean I had to do another Google search. :)

I found the following solution:

public IList<IPityTheFoo> FindByFoo(string foo)
        {
            return HibernateTemplate.SessionFactory.GetCurrentSession()
                .GetNamedQuery("StoredProcedureName")
                .SetParameter("Foo", foo)
                .List<IPityTheFoo>();
        }

The general idea here is that you need to call GetNamedQuery which expects the name of the query as defined by your Hibernate Mapping File:

<sql-query name="StoredProcedureName">
        <return class="IPityTheFoo">
            <return-property name="Foo" column="Foo"/>
        </return>
        exec [dbo].[StoredProcedureName] ?
    </sql-query>

The Spring.NET HibernateTemplate exposes the current session through the SessionFactory.GetCurrentSession(). To return the collection of your objects simpoly call List<T>().