I tried times when using NHibernate with MS SQL Server stored procedure but not successful.

Please help me solve this problem.

I want to call stored procedure not only selecting data on 1 table. It can select data on more than 1.

Thanks a lot!

what type result of you return?

13.2.2.1. 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.

Thanks for your response !

I used NHibernate 1.0.2 so I can not do that.
I know NHibernate 1.2 supports stored procedure so I will update my Nhibernate version.

pcphuc.