title: SQL Server: Effects of using 'WITH RECOMPILE' in proc definition?
body: <p>My understanding of the <code>WITH RECOMPILE</code> option with stored procedures is generally limited to using the clause with a single stored proc call as a trailing parameter:</p>
  
  <pre><code>exec sp_mystoredproc 'Parameter1', 2, '1/28/2011' with recompile
  </code></pre>
  
  <p>What are the effects of including <code>WITH RECOMPILE</code> in the actual proc definition?  Does this recompile the proc every time it's executed?  Or just the next time the proc is altered?</p>
  
  <p>Example:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[sp_mystoredproc]
      (@string1           varchar(8000)
      ,@int2              int = 2
      ,@dt_begin          DATETIME
      with recompile
  AS
  ... proc code ...
  </code></pre>