title: Known issue?: SQL Server 2005 stored procedure fails to complete with a parameter
body: <p>Your basic SP with a default parameter:</p>
  
  <pre><code>ALTER PROCEDURE [usp_debug_fails]
      @DATA_DT_ID AS int = 20081130
  WITH RECOMPILE
  AS
  BEGIN
      /*
          Usage:
          EXEC [usp_debug_fails] WITH RECOMPILE
      */
      -- Stuff here that depends on DATA_DT_ID
  END
  </code></pre>
  
  <p>The same SP with a local that is hardcoded.</p>
  
  <pre><code>ALTER PROCEDURE usp_debug_works]
  WITH RECOMPILE
  AS
  BEGIN
      /*
          Usage:
          EXEC [usp_debug_works] WITH RECOMPILE
      */
  
      DECLARE @DATA_DT_ID AS int
      SET @DATA_DT_ID = 20081130
      -- Stuff here that depends on DATA_DT_ID
  END
  </code></pre>
  
  <p>You can see where I put in the (redundant, even) <code>WITH RECOMPILE</code> options in order to avoid parameter sniffing (this was never necessary in development where this thing worked fine)</p>
  
  <p>The one that works completes fine in a minute or two, the other never completes - just sits there for hours.</p>
  
  <p>This problem never happened on the development server (build 9.00.3282.00), the production server is build 9.00.3068.00</p>
  
  <p>I've removed all kinds of code from the procs to try to get down to the minimal version which still exhibits the problem, and have been very careful to keep both versions of the SP the same except for that one parameter.</p>
  
  <p>I have plenty of other SPs which take parameters and they do run fine.  I've also <code>DROP</code>ped and re<code>CREATE</code>ed the SPs.</p>
  
  <p>Any ideas?</p>
  
  <p>And yes, I have a DBA looking at it and I do not have SHOWPLAN or any useful rights on production to see if there is blocking (in case one's plan results in a lock escalation I guess - again, the only difference is the parameter)</p>
  
  <p>I've reviewed all the SQL Server build information and don't see a known issue about this, so until I figure it out or the DBA figures it out, I'm kind of stuck.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>This also fails to complete (this is actually the normal form for these SPs - I just put a default in to make it easier to switch back and forth during testing)</p>
  
  <pre><code>ALTER PROCEDURE [usp_debug_fails]
      @DATA_DT_ID AS int
  WITH RECOMPILE
  AS
  BEGIN
      /*
          Usage:
          EXEC [usp_debug_fails] 20081130 WITH RECOMPILE
      */
      -- Stuff here that depends on DATA_DT_ID
  END
  </code></pre>
  
  <p>however this one completes (which may work as a workaround, although I have about 25 of these SPs to modify which all have the same form):</p>
  
  <pre><code>ALTER PROCEDURE [usp_debug_fails]
      @DATA_DT_ID_in AS int
  WITH RECOMPILE
  AS
  BEGIN
      /*
          Usage:
          EXEC [usp_debug_fails] 20081130 WITH RECOMPILE
      */
  
      DECLARE @DATA_DT_ID AS int
      SET @DATA_DT_ID = @DATA_DT_ID_in
      -- Stuff here that depends on DATA_DT_ID
  END
  </code></pre>