title: Counting the number of deleted rows in a SQL Server stored procedure
body: <p>In SQL Server 2005, is there a way of deleting rows and being told how many were <strong>actually</strong> deleted? </p>
  
  <p>I could do a <code>select count(*)</code> with the same conditions, but I need this to be utterly trustworthy.  </p>
  
  <p>My first guess was to use the <code>@@ROWCOUNT</code> variables - but that isn't set, e.g. </p>
  
  <pre><code>delete 
  from mytable 
  where datefield = '5-Oct-2008' 
  
  select @@ROWCOUNT 
  </code></pre>
  
  <p>always returns a 0. </p>
  
  <p>MSDN suggests the <a href="http://msdn.microsoft.com/en-us/library/ms189835.aspx" rel="noreferrer"><code>OUTPUT</code></a> construction, e.g. </p>
  
  <pre><code>delete from mytable 
  where datefield = '5-Oct-2008' 
  output datefield into #doomed
  
  select count(*) 
  from #doomed
  </code></pre>
  
  <p>this actually fails with a syntax error. </p>
  
  <p>Any ideas? </p>