NHibernate GetNamedQuery executes stored procedure but returns empty list

Question

I have a Stored Procedure that returns 2 records when tested in SQL Studio Management Express using this script:

declare @route_id_param as varchar(10), @start_time as datetime, @start_date as datetime, @end_date as datetime
set @start_time = GETDATE()
set @start_date = CONVERT(DATETIME,'10/26/2013',101) 
set @end_date = CONVERT(DATETIME,'12/26/2020',101)     

exec dbo.sp_get_deactivation_list @companyId=1, @startDate = @start_date, @endDate = @end_date;
select execution_time_in_ms = DATEDIFF(millisecond, @start_time, getdate())
GO

When I attempt to executed same stored procedure from NHibernate, using the following mapping file, I get 0 results.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping 
  xmlns="urn:nhibernate-mapping-2.2" 
  assembly="HGT.IridiumAirtime.Service" 
  namespace="HGT.IridiumAirtime.Service.Model">
  <sql-query name="GetDeactivationList" callable="true">
    <query-param name="companyId" type="int"/>
    <query-param name="startDate" type="DateTime"/>
    <query-param name="endDate" type="DateTime"/>
    <!--<return class="Activation">
      <return-property column="MobileId" name="MobileId" />
      <return-property column="RadioAddress" name="RadioAddress" />
      <return-property column="DeactivationDate" name="DeactivationDate" />
    </return>-->
    exec [sp_get_deactivation_list] @companyId=:companyId, @startDate=:startDate, @endDate=:endDate
  </sql-query>

</hibernate-mapping>

The method that executed said Stored Procedure is:

   public IEnumerable<TOut> ExecuteStoredProcedure<TOut>(string procedureName, IList<SqlParameter> parameters)
    {
        IEnumerable<TOut> result;

        using (var session = _sessionFactory.OpenSession())
        {
            var query = session.GetNamedQuery(procedureName);
            foreach (var parameter in parameters)
            {
                query.SetParameter(parameter.ParameterName, parameter.Value);
            }
            AddStoredProcedureParameters(query, parameters);
            result = query.List<TOut>(); 
        }

        return result;
    }

I even created a new mapping file for this Activation type even though there is no associated table but NHibernate does not return the correct result. Un-commenting the type mapping definition within the procedure's mapping file does not change outcome. Am I missing something here?

I get no exceptions, so it looks like procedure is being executed.

UPDATE: 1 Removed call to AddStoredProcedureParameters and replaced with method body.

UPDATE 2 Adding Stored Procedure:

if OBJECT_ID ( 'dbo.[sp_get_deactivation_list]', 'P' ) is not null 
    drop procedure dbo.[sp_get_deactivation_list];
go

create procedure [dbo].[sp_get_deactivation_list]   
    @companyId int,
    @startDate DateTime,
    @endDate DateTime
as
begin       
    select 
        assMobileRadio.Mobile_ID as MobileId, 
        tblRadioinfo.Radio_Address as RadioAddress, 
        tblRadioinfo.Deactivation_Date as DeactivationDate
    from tblRadioinfo 
    left join assMobileRadio 
    on tblRadioinfo.Radio_ID = assMobileRadio.Radio_ID
    where tblRadioinfo.Radio_Type_ID in (2, 4, 7) 
    and tblRadioinfo.Company_ID = @companyId
    and tblRadioinfo.Deactivation_Date <= @endDate
    and tblRadioinfo.Deactivation_Date >= @startDate
    and tblRadioinfo.Radio_Address in (select IMEI from [airtime_cdrs] where  Effective_Date > @startDate and Effective_Date < @endDate)
from airtimes_cte for xml path('')),1,1,''))    
    ORDER BY tblRadioinfo.Deactivation_Date
end

Solution 1

I've just tried your mapping (the same as possible) and your 1) mapping and 2) call is OK. Except the fact that I am not sure about the call inside of the AddStoredProcedureParameters

So, this is working:

var query = session.GetNamedQuery("GetDeactivationList");
    query.SetParameter("companyId", 1);
    query.SetParameter("startDate", new DateTime(2013,10,26));
    query.SetParameter("endDate", new DateTime(2020,12,26));

// if the <return class="Activation"> is commented ... we'll get object[]
var list = query.List<object[]>(); 

And this is working. Other words. Your mapping is working. The SQL call (exec sp) is fired, because if the named query won't be find, or some other issues would happen, we will get an Exception.

The only suspected right now is the black box AddStoredProcedureParameters (could not it switch start and end date?)

I would suggest, do check your Unit test in the SQL Server Profiler, you will see what is passed to DB... and you can take and reexecute.

Solution 2

If executing the stored procedure you have is your main purpose (not exactly using GetNamedQuery), you can use the following:

Assuming that your methods' arguments are: int? id, DateTime? fromDate, DateTime? toDate (all nullable values)

IList<TOut> result = session.CreateSQLQuery("exec [dbo].[sp_get_deactivation_list]:param1, :param2, :param3")
    .SetParameter("param1", id, NHibernateUtil.Int32)
    .SetParameter("param2", fromDate, NHibernateUtil.DateTime)
    .SetParameter("param3", toDate, NHibernateUtil.DateTime)
    .SetResultTransformer(Transformers.AliasToBean<TOut>()).List<TOut>();

return result;

And in your mapping file for class TOut, just have the related properties as normal.