Consuming return value from stored procedure using NHibernate without mapping

After haggling around I have found a solution that works. First I have created a stored procedure as follows:

CREATE PROCEDURE GETTOTALINCOME
AS 
DECLARE
     @TOTALINCOMECOUNT MONEY;
BEGIN
     SELECT @TOTALINCOMECOUNT = SUM(INVAMOUNT) FROM INVOICE
     RETURN @TOTALINCOMECOUNT;
END;

This is a simple stored procedure that just returns a value. Now lets look at NHibernate code block that can execute the stored procedure and read the return value.

using (var session = OpenSession())
{
     var query = session.CreateSQLQuery("EXEC GETTOTALINCOME");
     object result = query.UniqueResult();
     retVal = Convert.ToInt64(result);
}

I am getting exceptions so I started debugging it and have found "result" is null. I have looked at some articles of using stored procedure, but I do not want to define mapping either FulentNHibernate mapped classes or .hbm configuration.

I have looked again into stored procedure definition, NHibernate can read the values that are returned by a "SELECT" statement but not "RETURN". So I have changed my stored procedure as follows:

CREATE PROCEDURE GETTOTALINCOME
AS 
DECLARE
     @TOTALINCOMECOUNT MONEY;
BEGIN
     SELECT @TOTALINCOMECOUNT = SUM(INVAMOUNT) FROM INVOICE
     SELECT @TOTALINCOMECOUNT;
END;

Now this works.

“We have to stop optimizing for programmers and start optimizing for users.” – Jeff Atwood