How to get the return value from a SQL Server Stored procedure into nHibernate?

1.Database platform: SqlServer

2.Data Access: nHibernate 1.2

  • SQL Server: CTE, how to get last row number
  • close/kill transaction
  • SQL Update Performance
  • TSQL with SQLAlchemy does not execute
  • Mutiplication aggregate operator in SQL
  • Which version of SQL Server I would need to purchase in order to be able to legally include in a web app I am going to package?
  • Now we need access the store procedure by nHibernate,like this:

        Select * From User 
        Return 1234 

    I know I can get the User List by IQuery,
    And I want to get the default return value “1234” too.


    1. How to get this default return value?
    2. If can’t get it directly , can we get the value by output parameter?

    4 Solutions collect form web for “How to get the return value from a SQL Server Stored procedure into nHibernate?”

    this is how i do it:

    in my .hbm.xml

    <?xml version="1.0" encoding="utf-8" ?>
    <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DocumentManagement.Data"  namespace="DocumentManagement.Data.Repositories" >
    <sql-query name="GetDocument">    
    <return class="DocumentManagement.Core.Models.PhysicalDocument, DocumentManagement.Core">      
        <return-property column="DocId" name="Id" />      
        <return-property column="Filepath" name="Filepath" />
        <return-property column="Filename" name="Filename" />
    exec Investor_GetDocumentById :userId, :docId

    in my repository.cs

        public PhysicalDocument GetDocumentPath(int userId, int docId)
            var query = Session.GetNamedQuery("GetDocument")
                .SetInt32("userId", userId)
                .SetInt32("docId", docId).List<PhysicalDocument>();
            return query[0];

    NHibernate does not let you use stored procedures in this manner. But it does allow a way to make calls using the plain old ADO.NET API. The NHibernate Documentation says that if you want to use these procedures you have to execute them via session.Connection. Here’s an example –

    ISession session = sessionFactory.GetSession();
    using(ITransaction transaction = session.BeginTransaction())
       IDbCommand command = new SqlCommand();
       command.Connection = session.Connection;
       // Enlist IDbCommand into the NHibernate transaction
       command.CommandType = CommandType.StoredProcedure;
       command.CommandText = "dbo.SetUserInfo";
       // Set input parameters
       var parm = new SqlParameter("@UserID", SqlDbType.Int);
       parm.Value = 12345;
       // Set output parameter
       var outputParameter = new SqlParameter("@Quantity", SqlDbType.Int);
       outputParameter.Direction = ParameterDirection.Output;
       // Set a return value
       var returnParameter = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
       returnParameter.Direction = ParameterDirection.ReturnValue;
       // Execute the stored procedure

    You can find more details here –

    First of all, that’s not called a “default return value” anywhere I’ve ever seen. It’s just the return value. It’s usually used to return a success / error status.

    I don’t know how nHibernate does things, but in ADO.NET, you’d use a parameter with the Direction property set to “Return”. Maybe there’s an equivalent in nHibernate.

    OTOH, it would be more usual to use an OUTPUT parameter to return an actual useful value, and keep the RETURN value for error codes, or for being ignored.

    I’ve done this before (not in nHibernate).

    You must completely process the entire recordset before retrieving the output parameters.

    Another discussion

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.