Hi all,

Here is the Complete working sample code:


Procedure Code:
===========

In SQL Server 2005

ALTER PROCEDURE [dbo].[C_CLD_FCLD_NHIB_TEST_PROC]
(
@FISCALYEARID BIGINT
)
AS
BEGIN
SET NOCOUNT ON;
SELECT FiscalMonthID,StartDate,EndDate,NumberOfDaysInMonth
FROM C_CLD_FCLD_FISCALMONTHS
WHERE FiscalYearID = @FISCALYEARID
END


hbm Configuration
============
<?xml version="1.0" encoding="utf-8" ?>
<!-- Generated by MoreGen 28-Apr-2008 11:27:28 -->
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Business.Common.CalendarDefinition.FiscalCalendarDefinition"
assembly="Business">
<class name="NhibProcSample" table="C_Cld_Fcld_FiscalMonths" lazy="true">
<id name="Id" column="FiscalMonthID">
<generator class="native" />
</id>

<property name="StartDate" column="StartDate" />
<property name="EndDate" column="EndDate" />
<property name="NumberofDaysinMonth" column="NumberofDaysinMonth" />
<loader query-ref="C_CLD_FCLD_NHIB_TEST_PROC"/>
</class>
<sql-query name="C_CLD_FCLD_NHIB_TEST_PROC" >
<return alias="C_CLD_FCLD_NHIB_TEST_PROC" class="NhibProcSample">
<return-property name="Id" column="FiscalMonthId"/>
<return-property name="StartDate" column="StartDate"/>
<return-property name="EndDate" column="EndDate"/>
<return-property name="NumberofDaysinMonth" column="NumberofDaysinMonth"/>
</return>
exec C_CLD_FCLD_NHIB_TEST_PROC :FISCALYEARID
</sql-query>
</hibernate-mapping>


C# Code:
======

This will take care of Single row and Collection

IQuery query = (IQuery)Session.GetNamedQuery("C_CLD_FCLD_NHIB_TEST_PROC");
query.SetParameter("FISCALYEARID", fiscalYearId);
System.Collections.IList result = query.List();

Rules/limitations for using stored procedures:

To use stored procedures with NHibernate the procedures/functions have to follow some rules. If they do not follow those rules they are not usable with NHibernate. If you still want to use these procedures you have to execute them via session.Connection. The rules are different for each database, since database vendors have different stored procedure semantics/syntax.

Stored procedure queries can't be paged with SetFirstResult()/SetMaxResults().

Recommended call form is dependent on your database. For MS SQL Server use exec functionName <parameters>.

For Oracle the following rules apply:

A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type, see Oracle literature.

For MS SQL server the following rules apply:

The procedure must return a result set. NHibernate will use IDbCommand.ExecuteReader() to obtain the results.

If you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement.


[/url]