Calling a Stored Procedure from NHibernate and retrieving @@ROWCOUNT
<p>After trying to work this out for a few hours, I'm beginning to think I can't solve this without your help. Situation:</p>

<p>My stored procudure in SQL Server Management Studio (basically):</p>

<pre><code>SET NOCOUNT ON;
DELETE myTable
...                    -- Complex join query that deletes duplicates
RETURN @@ROWCOUNT      -- The number of rows affected
</code></pre>

<p>This procedure works well, if I run it in SSMS the following query is run:</p>

<pre><code>USE myDb
GO
DECLARE @return_value int
EXEC    @return_value = [dbo].[StoredProcedure]
        @var1 = N'asd',
        @var2 = N'fgh',
        @var3 = N'jkl'
SELECT  'Return Value' = @return_value
GO
</code></pre>

<p>The result is shown as 1 row with name <code>Return Value</code> and value <code>8700</code> (this is <strong>correct</strong>, it's the number of rows that was deleted by the query).</p>

<p>My problems begin in my C#/NHibernate code. In my NHibernate mapping:</p>

<pre><code>&lt;sql-query name="MyStoredProcedureName"&gt;
  exec dbo.StoredProcedure @var1=:var1, @var2=:var2, @var3=:var3
&lt;/sql-query&gt;
</code></pre>

<p>In my method where I call the stored procedure:</p>

<pre><code>var deletedEntities = session.GetNamedQuery("MyStoredProcedureName")
                             .SetString("var1", "asd")
                             .SetString("var2", "fgh")
                             .SetString("var3", "jkl")
                             .ExecuteUpdate();
</code></pre>

<p>This results in <code>deletedEntities = -1</code>. I would like it to be <code>deletedEntities = 8700</code>.</p>

<p>I'd like to point out that the following <strong>did</strong> return the number of affected rows:</p>

<pre><code>var deletedEntities = session.CreateQuery("DELETE Class c WHERE c.var1 = :var1 AND c.var2 = :var2 AND c.var3 = :var3")
                             .SetString("var1", var1)
                             .SetString("var2", var2)
                             .SetString("var3", var3)
                             .ExecuteUpdate();
</code></pre>

<p>Where <code>Class</code> is the class that belongs to the NHibernate mapping.</p>

<p>So, to summarize:</p>

<ol>
<li>ExecuteUpdate on a SIMPLE <code>session.CreateQuery</code> gives me back the number of affected rows (GOOD)</li>
<li>ExecuteUpdate on a COMPLEX <code>session.GetNamedQuery</code> gives me back <code>-1</code> (BAD)</li>
<li>This same complex stored procedure gives me back the desired <code>8700</code> when I execute it in SSMS (GOOD)</li>
</ol>

<p>Anyone has an idea of how to fix 2?</p>