title: Linq To Sql : Call a Stored Procedure With Different Return Values
body: <p>I have a stored procedure that, depending on the input, returns multiple rows of ints or multiple rows of DateTimes.</p>
  
  <p>I have looked at the answers to <a href="https://stackoverflow.com/questions/284558/linq-to-sql-stored-procedure-with-different-return-types-based-on-an-if-else">this question</a> and have read and implemented Scott Guthries solution from <a href="http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx" rel="nofollow noreferrer">Handling Multiple Result Shapes from SPROCs</a> (scroll down the page to see that section.)</p>
  
  <p>Unfortunately, it just is not working.</p>
  
  <p>Here is my code:</p>
  
  <pre><code>    [Function(Name = "FunkyStoredProcedure")]
      [ResultType(typeof(List&lt;int&gt;))]
      [ResultType(typeof(List&lt;DateTime&gt;))]
      public IMultipleResults FunkyStoredProcedure(int appId, int sId, int cId)
      {
          IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)(MethodInfo.GetCurrentMethod()), appId, sId, cId);
          return (IMultipleResults)result.ReturnValue;
  
      }
  }
  </code></pre>
  
  <p>Using SQL Server Profiler, Linq 2 Sql is producing code that looks like this:</p>
  
  <pre><code>declare @p6 int
  set @p6=0
  exec sp_executesql N'EXEC @RETURN_VALUE = [FunkyStoredProcedure] @appId = @p0, @sId = @p1, @cId = @p2',N'@p0 int,@p1 int,@p2 int,@RETURN_VALUE int output',@p0=2,@p1=4,@p2=2,@RETURN_VALUE=@p6 output
  select @p6
  </code></pre>
  
  <p>Which produces a result set that looks like this:</p>
  
  <pre><code>cId
  -----------
  694
  42
  43
  41
  4732
  
  
  -----------
  0
  
  (1 row(s) affected)
  </code></pre>
  
  <p>Note that there are two result sets here (there should be only one.) And note that the second result set, the one that is being returned to LinqToSql is 0!</p>
  
  <p>The line "exec sp_executesql..." produces the first, correct, result set.</p>
  
  <p>The line "select @p6" produces the second, incorrect, result set.</p>
  
  <p>First of all, why is @p6 being defined as an int? In the function I have two possible ResultTypes : List and List This seems like an issue right here.</p>
  
  <p>Second, why is it not returning the output of the stored procedure? And instead returning the value of @p6?</p>
  
  <p>Finally, most important, how do I get this to work correctly?</p>