For those of you who are having trouble locating an example of how to actually execute a stored procedure with NHibernate, please see the below code that I use to execute mine.

*** MAPPING FILE ***
<sql-query name="MyStoredProcedure">
<return alias="MyObject" class="My.Namespace.MyObject, My.Namespace">
<return-property name="Id" column="ID"/>
<return-property name="Username" column="USERNAME"/>
</return>
exec MyStoredProcedure :PrimaryKey, :Username
</sql-query>

*** EXECUTION CODE (c#) ***
ISession session = base.NHibernateSession;
IQuery query = (IQuery)session.GetNamedQuery("MyStoredProcedure");
query.SetParameter("PrimaryKey", theValue);
query.SetParameter("Username", theValue);
return (MyObject)query.UniqueResult();

Hope this helps. I spent many hours trying all different combinations until this finally worked.

Hi Chris,

Well done. I also took a while to figure that one out. I am still having trouble though. I get some wried errors when trying to run the stored proc.

Message = "could not execute query\r\n[ exec sproc_GetStrategyById ? ]\r\n Name: SgyId - Value: 14\r\n[SQL: exec sproc_GetStrategyById ?]"

This is what the inner exception says.

[System.IndexOutOfRangeException] = {"sgy2_1_0_"}

It gives very little information as to the exact nature of the failure.

Any chance you could post the sample Stored Proc. Did you see any error like this at all. I thought it was a mapping issue but I am pretty sure all the mapping is ok.