title: SQL-CLR stored procedure doesn't return value
body: <p>I looked through Stackoverflow and found almost identical <a href="https://stackoverflow.com/questions/8070403/clr-stored-procedure-return-value-null">question here</a>. It was asked a year ago and nobody answered it yet. Maybe I'll be more lucky than user1038334 and somebody will help me.</p>
  
  <p>I have a SQL CLR stored procedure which works fine for days until something weird happens. My stored proc updates tables and returns a value as a result code. So by looking at this value I can decide if something went wrong. The problem is in receiving this result value. C# code throws an exception at a point of receiving a return value.</p>
  
  <pre><code>var returnValue = new SqlParameter {Direction = ParameterDirection.ReturnValue};
  command.Parameters.Add(returnValue);
  
  connection.Open();
  command.ExecuteNonQuery();
  
  return (int)returnValue.Value; //&lt;-- here is an exception
  </code></pre>
  
  <p>And the funniest thing is that if I connect SQL Profiler, catch the query and then execute this query inside of SQL Server Management Studio I still can get a result value without any problems:</p>
  
  <pre><code>DECLARE @RC INT
  EXECUTE @RC = MyClrStoreProc
  SELECT @RC
  </code></pre>
  
  <p>Once I republish the CLR assembly or restart SQL Server everything gets fixed.</p>
  
  <p>I'm pretty sure there should be a reason for such weird behaviour but I can't find.</p>
  
  <ul>
  <li><p>SQL Server version: Microsoft SQL Server Developer Edition (64-bit) - v.10.50.1765.0</p></li>
  <li><p>Host OS version: Microsoft Windows NT 6.1 (7601)</p></li>
  <li><p>.Net version: v4.0.30319</p></li>
  </ul>
  
  <p>Any help would be greatly appreciated.</p>