Getting return values from an SP in NHibernate

In order to get a return value from a stored procedure in NHibernate, you'll need to, depending on the returned value, either (for singular column returned values):

1. Create your sp in your db.
2. Call it using the Session.CreateSQLQuery("exec myStoredProc")

Or (for 'complex', multiple column returned values):

1. Create your sp
2. map it.
3. Call it.

First and foremost, remember always to use SELECT in your SP rather than RETURN.
Now, for the first case, this would look like this:
CREATE PROCEDURE [dbo].[GetIt]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.


-- Insert statements for procedure here
SELECT 1342
END

This would be then called like this:
int query = session.CreateSQLQuery("exec GetIt").List()[0];

As for the second case, this would look like this:
ALTER PROCEDURE [dbo].[GetIt]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.


-- Insert statements for procedure here
SELECT 1342 as Id, 'MyName' as Name
END

The mapping would then be:








exec GetIt




And finally, this would be executed like this:
IList users = session.GetNamedQuery("GetIt").List();
Beware that the return class referenced in the sql-query mapping should be mapped itself on its own, separately!