title: Calling Stored Procedure using NHibernate as a Named Query with Input Parameters
body: <h2>Problem Description:</h2>
  
  <p>I am trying to execute a stored procedure with an input parameter. The stored procedure executes correctly when run from MSSQL 2008 SQL Studio. However I get an error while running it as a named query using NHibernate.</p>
  
  <p>I get the error at the time of SessionFactory configuration. 
  <strong><em>Hence I believe that I am not mapping the named query correctly.</em></strong></p>
  
  <h2>Stored Procedure Details:</h2>
  
  <p><strong>Name of the Procedure</strong> : CASCADE_POSITIONTEMPLATE_PERMISSIONS <BR/>
  <strong>Input Parameter</strong>       : PositionTemplateId<BR/></p>
  
  <p>The stored procedure returns a count which I want to return encapsulated  in the <code>PositionTemplateUpdateCascadeResult</code> class which has the specified property.</p>
  
  <h2>Named Query/Class Mapping:</h2>
  
  <p>Named Query Mapping:</p>
  
  <pre><code>    &lt;hibernate-mapping default-cascade="none" xmlns="urn:nhibernate-mapping-2.2" 
  assembly="StudentVoiceGroups.Entities" namespace="StudentVoiceGroups.Entities" &gt;
        &lt;sql-query name="CASCADE_POSITIONTEMPLATE_PERMISSIONS" cacheable="false"&gt;
          &lt;return class="PositionTemplateUpdateCascadeResult" alias="result"&gt;
            &lt;return-property name="UpdatedPositionsCount"&gt;
              &lt;return-column name="UpdatedPositionsCount" /&gt;
            &lt;/return-property&gt;
          &lt;/return&gt;
          exec CASCADE_POSITIONTEMPLATE_PERMISSIONS :PositionTemplateId
        &lt;/sql-query&gt;
      &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>Following is the class which I want to be returned:</p>
  
  <pre><code>public class PositionTemplateUpdateCascadeResult
  {
      public int UpdatedPositionsCount { get; set; }
  }
  </code></pre>
  
  <p>when I execute the procedure in MSSQL Studio as: <br/></p>
  
  <pre><code>   EXEC [CASCADE_POSITIONTEMPLATE_PERMISSIONS]  15
  </code></pre>
  
  <p>I get the following <br/><br/></p>
  
  <p><img src="https://i.stack.imgur.com/3HxG7.jpg" alt="enter image description here"></p>
  
  <p>Let me know if any thing else is needed.</p>
  
  <hr>
  
  <p>EDIT: I was able to make this work when I removed the return class:
  Following mapping works correctly:</p>
  
  <pre><code>&lt;hibernate-mapping default-cascade="none" xmlns="urn:nhibernate-mapping-2.2" 
      assembly="StudentVoiceGroups.Entities" namespace="StudentVoiceGroups.Entities" &gt;
  &lt;sql-query name="CASCADE_POSITIONTEMPLATE_PERMISSIONS" cacheable="false"&gt;            
        exec CASCADE_POSITIONTEMPLATE_PERMISSIONS :PositionTemplateId
  &lt;/sql-query&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>Should the return class be an entity which is mapped to a table? In my case it is a simple class. I was of the opinion that the return class is just used like we use <code>ResultTransformer</code>.</p>