title: How do I get Linq to SQL to recognize the result set of a dynamic Stored Procedure?
body: <p>I'm using Linq-to-SQL with a SQL Server backend (of course) as an ORM for a project. I need to get the result set from a stored procedure that returns from a dynamically-created table. Here's what the proc looks like:</p>
  
  <pre><code>CREATE procedure [RetailAdmin].[TitleSearch] (
  @isbn varchar(50), @author varchar(50),
   @title varchar(50))
  as
  
  declare @L_isbn varchar(50)
  declare @l_author varchar(50)
  declare @l_title varchar(50)
  declare @sql nvarchar(4000)
  
  set  @L_isbn = rtrim(ltrim(@isbn))
  set @l_author = rtrim(ltrim(@author))
  set @l_title = rtrim(ltrim(@title))
  
  CREATE TABLE #mytemp(
      [storeid] int not NULL,
      [Author] [varchar](100) NULL,
      [Title] [varchar](400) NULL,
      [ISBN] [varchar](50) NULL,
      [Imprint] [varchar](255) NULL,
      [Edition] [varchar](255) NULL,
      [Copyright] [varchar](100) NULL,
      [stockonhand] [int] NULL
  )
  
  set @sql  = 'select  a.storeid, Author,Title, thirteendigitisbn ISBN,  
  Imprint,Edition,Copyright ,b.stockonhand from ods.items a join ods.inventory b on     
  a.itemkey = b.itemkey where b.stockonhand &lt;&gt; 0  ' 
  
  if len(@l_author) &gt; 0
  set @sql =  @sql + ' and author like ''%'+@L_author+'%'''
  
  if len(@l_title) &gt; 0
  set @sql =  @sql + ' and title like ''%'+@l_title+'%'''
  
  if len(@L_isbn) &gt; 0
  set @sql =  @sql + ' and thirteendigitisbn like ''%'+@L_isbn+'%'''
  
  print @sql
  
  if len(@l_author) &lt;&gt; 0 or len(@l_title) &lt;&gt;  0 or len(@L_isbn) &lt;&gt; 0 
  
  begin
      insert into #mytemp
      EXECUTE sp_executesql  @sql
  end 
  
  
  select * from #mytemp
  drop table #mytemp
  </code></pre>
  
  <p>I didn't write this procedure, but may be able to influence a change if there's a really serious problem.</p>
  
  <p>My present problem is that when I add this procedure to my model, the designer generates this function:</p>
  
  <pre><code>[Function(Name="RetailAdmin.TitleSearch")]
  public int TitleSearch([Parameter(DbType="VarChar(50)")] string isbn,  
    [Parameter(DbType="VarChar(50)")] string author, 
    [Parameter(DbType="VarChar(50)")] string title)
  {
      IExecuteResult result = this.ExecuteMethodCall(this, 
          ((MethodInfo)(MethodInfo.GetCurrentMethod())), isbn, author, title);
  
      return ((int)(result.ReturnValue));
  }
  </code></pre>
  
  <p>which doesn't look anything like the result set I get when I run the proc manually:</p>
  
  <p><img src="https://lh5.ggpht.com/_g6k7SWc-6HY/SYMrST6oPaI/AAAAAAAAAE8/KD6-NFPxcN0/s400/storedproc.jpeg" alt="Result Set"></p>
  
  <p>Can anybody tell me what's going wrong here?</p>
  
  <p>This is basically the same problem as <a href="https://stackoverflow.com/questions/404186/problem-with-the-return-type-of-a-stored-procedure">this question</a> but due to the poor phrasing from the OP it was never really answered.</p>
  
  <hr>
  
  <p>Thanks Marc for your reply. I will see about making the changes you suggested.</p>
  
  <p>The problem was the temp table. Linq to Sql just doesn't know what to do with them. This was particularly difficult to diagnose, because Visual Studio caches information about stored procs, so when it initially failed to find a result set it set the return as a default integer type and didn't update when I made changes to the stored proc. Getting VS to recognize a change requires you to:</p>
  
  <ul>
  <li>Delete proc from the dbml</li>
  <li>delete the server connection from Server Explorer</li>
  <li>save the dbml to force a recompile</li>
  <li>close the project and restart VS</li>
  <li>recreate the server connection and import the proc</li>
  </ul>
  
  <p>You might not have to do every one of those steps, but that's what worked for me. What you need to do, if you must use a temp table, is to create a barebones proc that simply returns the correct schema, and then alter it to do what you want after you've imported it into the OR Designer.</p>