Nhibernate CreateSQLQuery Stored Procedure result to non mapped class

asp.net-mvc, hql, nhibernate, stored-procedures

Solution

The answer is hidden in the exception (and mostly in this part: "Incorrect syntax near '@p1'."):

{"could not execute query\r\n[ exec PER_PrikklokSaldi @p0 @p1 @p2 ]\r\n Name:IDPers - Value:827 Name:jaar - Value:2014 Name:maand - Value:1\r\n[SQL: exec PER_PrikklokSaldi @p0 @p1 @p2]"}

The parameters should be split by comma:

var test = Session.CreateSQLQuery("exec PER_PrikklokSaldi :IDPers, :jaar, :maand")
        .SetParameter("IDPers", _employeeId)
        .SetParameter("jaar", _year)
        .SetParameter("maand", _month)

or maybe rather this (for sql server)

"exec PER_PrikklokSaldi @IDPers=:IDPers, @jaar=:jaar, @maand=:maand"

To execute this instead

exec PER_PrikklokSaldi @p0, @p1, @p2

Problem

In my application I want to get data from a stored procedured where the table is not mapped in the application. In this stored procedure I added aliases to the column names which respond to the properties in my class. HQL: ``` return Session.CreateSQLQuery("exec PER_PrikklokSaldi :IDPers :jaar :maand") .AddScalar("Description", NHibernateUtil.String) .AddScalar("StartSaldo", NHibernateUtil.Int32) .AddScalar("Plus", NHibernateUtil.Int32) .AddScalar("Minus", NHibernateUtil.Int32) .AddScalar("EndSaldo", NHibernateUtil.Int32) .SetParameter("IDPers", _employeeId) .SetParameter("jaar", _year) .SetParameter("maand", _month) .SetResultTransformer(new AliasToBeanResultTransformer(typeof(ClockInfoSaldi))) .List<ClockInfoSaldi>() .ToList(); ``` Class: ``` public class ClockInfoSaldi { public string Description { get; set; } public int StartSaldo { get; set; } public int Plus { get; set; } public int Minus { get; set; } public int EndSaldo { get; set; } } ``` Stored Procedure result in MS SQL Management Studio: I can run this: ``` var test = Session.CreateSQLQuery("exec PER_PrikklokSaldi :IDPers :jaar :maand") .SetParameter("IDPers", _employeeId) .SetParameter("jaar", _year) .SetParameter("maand", _month) ``` But when I run the first mentioned HQL code I get this error: {"could not execute query\r\n[ exec PER_PrikklokSaldi @p0 @p1 @p2 ]\r\n Name:IDPers - Value:827 Name:jaar - Value:2014 Name:maand - Value:1\r\n[SQL: exec PER_PrikklokSaldi @p0 @p1 @p2]"} InnerException: {"Incorrect syntax near '@p1'."}

Original source