title: NHibernate - Returning an output parameter from a mapped Stored Procedure
body: <p>I have a Stored Procedure that I have mapped via NHibernate. The Sproc returns both a resultset, and an output paramter, basically a count of the total records followed by the resultset itself. Something like this:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[mySproc]
  (
      @StartRecord            INT = 1,
      @EndRecord              INT = 10,
      @TotalRecords       INT OUTPUT
  )
  AS
  BEGIN
      SET NOCOUNT ON
  
      // Do a count
      SELECT @TotalRecords = COUNT(DISTINCT x.Id)
      FROM Blah ...snip
  
      // Perform a query with paging
      SELECT
          x.Id,
          x.Column1
      FROM ...some really complex query that uses paging
  END
  </code></pre>
  
  <p>I'm implementing paging via a Sproc and thus need to get the total number of records. The query is quite complex with joins across several tables, potentially quite slow, and thus needs the care, fine tuning and optimisation from writing T-SQL directly.</p>
  
  <p>My problem is that I can't get at the OUTPUT parameter in the Sproc (@TotalRecords). I have mapped this Sproc like so:</p>
  
  <pre><code>&lt;class name="MyLibrary.SomeClass, MyLibrary"&gt;
  
      &lt;id name="Id" type="Int32" /&gt;
      &lt;property name="Column1" type="String" length="50" not-null="false" /&gt;
  
  &lt;/class&gt;
  
  &lt;sql-query name="mySproc"&gt;
      &lt;return class="MyLibrary.SomeClass, MyLibrary"&gt;
          &lt;return-property name="Id" column="Id"/&gt;
          &lt;return-property name="Column1" column="Column1"/&gt;
      &lt;/return&gt;
  
      EXEC mySproc
          @StartRecord = :startRecord,
          @EndRecord = :endRecord,
          @TotalRecords = 0
  
  &lt;/sql-query&gt;
  </code></pre>
  
  <p>And I can get at the resultset like so:</p>
  
  <pre><code>IList&lt;SomeClass&gt; records = Session.GetNamedQuery("mySproc")
      .SetParameter("startRecord", 1, NHibernateUtil.Int32)
      .SetParameter("endRecord", 20, NHibernateUtil.Int32)
      .List&lt;SomeClass&gt;();
  </code></pre>
  
  <p>But how do I get the @TotalRecords output parameter?</p>