title: Workaround for WITH RESULT SET for SQL Server 2012
body: <p>I have a database (several actually) under SQL Server 2008 running perfectly fine.</p>
  
  <p>Upon upgrading to 2012, I find that none of my stored procedures with dynamic SQL will function without throwing an error.</p>
  
  <p>Stuff like:</p>
  
  <pre><code>SET @SQLQuery = ' 
  WITH CTE AS
  (   SELECT  p.*
              ,RowNum = ROW_NUMBER() OVER(PARTITION BY pbd.sku 
                      ORDER BY pbd.sku)
      FROM    Products_BaseData pbd
      LEFT JOIN Products as p on p.sku = pbd.sku
      WHERE pbd.Products_BaseData_productGUID in (
          SELECT ptcprodGUID FROM Products2Categories WHERE ptccatID = @catId
      ) AND pbd.VisibleOnSite = 1
  )
  -- blah blah --
      WHERE RowNum = 1 '
      + @filter + '
          ORDER BY
          CASE WHEN @sortBy = ''0'' then ProductName END ASC,
          CASE WHEN @sortBy = ''1'' then ProductName END DESC
  '
  SET @ParamDefinition = ' @catId int,
                           @sortBy int'
  EXECUTE sp_Executesql @SQLQuery, @ParamDefinition,
                      @catId,
                      @sortBy
  </code></pre>
  
  <p>This results in...</p>
  
  <blockquote>
    <p>The metadata could not be determined because statement 'EXECUTE sp_Executesql @SQLQuery, @ParamDefinition,
        @catId,
        @sortBy' in procedure 'jspc_GetVisibleProductsBaseByCategory'  contains dynamic SQL.  Consider using the WITH RESULT SETS clause to explicitly describe the result set.</p>
  </blockquote>
  
  <p>Consider? No. I don't want to use result sets. In fact I can't in several circumstances as the data returned may well be different depending on input parameters.</p>
  
  <p>I have tried changing the compatibility level all the way back to 80 and it still throws the error. I tried using "SQLOLEDB" and "SQLNCLI10" in the loopback linked server. Made no difference. I also tried using "System.Data.OleDb" as the provider for the connection string in <code>web.config</code>.</p>
  
  <p>I do not want to go through several hundred stored procedures in multiple databases and edit them one by one and go through testing each one.</p>
  
  <p>What do I need to do to get a SQL Server 2008 database to function <strong>AS IS</strong> on SQL Server 2012? I mean there has to be a flag or something I can set to tell 2012 to run them as is?</p>
  
  <p>Edit...
  Adding code showing a typical call from C#:</p>
  
  <pre><code>try {
      using ( SqlConnection SqlConn = new SqlConnection( StrConn ) ) {
          SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
          SqlCmd.CommandText = "jspc_GetVisibleProductsBaseByCategoryPaged";
          SqlCmd.Connection = SqlConn;
          SqlCmd.Parameters.AddWithValue( "@catId", catId );
          SqlCmd.Parameters.AddWithValue( "@sortBy", sort );
          SqlCmd.Parameters.AddWithValue( "@filter", filter );
          SqlCmd.Parameters.AddWithValue( "@PageStart", page );
          SqlCmd.Parameters.AddWithValue( "@ItemCount", ItemCount );
          SqlDA.SelectCommand = SqlCmd;
          SqlDA.Fill( dtRetVal );
      }
  }
  </code></pre>