title: SQL ExecuteScalar doesn't throw exception when insert used with output clause
body: <p>I came across an interesting issue with the "ExecuteScalar()" method of the SqlCommand object.</p>
  
  <p>I have a stored procedure, which creates a new record in a table. It contains an "insert" statement which has an "output" clause <em>(as the created record utilises an identity column and some default values)</em>.</p>
  
  <p>This works fine, and the "ExecuteScalar" returns the identity column value <em>(1st row, 1st column)</em>.</p>
  
  <p>However, in testing, I deliberately call my stored procedure twice, and on the second time it is expected to hit a unique constraint in the data table and fail <em>(there's a "try/catch/throw" in the SP)</em>.</p>
  
  <p>So far so good, except the 2nd call returns an empty rowset <em>(as I can verify if I run this from SQLMgr)</em> and ExecuteScalar returns a null - <strong>but</strong> doesn't throw the exception.</p>
  
  <p>This isn't what was expected and leaves me with an interesting question. How can I utilise ExecuteScalar - in order to, in a success scenario, return the id column value - but have it fail should an error occur ?</p>
  
  <p>I know that if I remove the "output" clause from my "insert", the exception is thrown as would be expected.</p>
  
  <p>If this is expected behaviour, how should I call my SP, in order to return the id value, and have the exception raised should my constraint be hit ?</p>
  
  <p>Obviously, I could do all manner of jiggery-pokery in the stored procedure <em>(like insert then select using scope_identity)</em>, and - as I have done in my tests - I could check for a null return and make a subsequent ExecuteNonQuery call <em>(which does throw the exception)</em>. But that all seems a bit of a fudge, given the usefulness of the "output" clause.</p>
  
  <p>I have done some "Googling", and have found a reference to ExecuteScalar "eating" exceptions, but it was only a passing reference.</p>
  
  <p>Ideas ?</p>
  
  <p><strong>-- edit 1 --</strong></p>
  
  <p>What I'm saying, upon further investigation writing the "simple case" below, is that (apparently) if I have a stored procedure, which has a "try catch", and an insert which violates a unique constraint, when invoked via ExecuteScalar, I do not get an exception.</p>
  
  <ul>
  <li><p>If I remove the "try/catch/throw" - I get the exception.</p></li>
  <li><p>If I remove the "output" - I get the exception</p></li>
  </ul>
  
  <p><strong>-- edit 2 --</strong></p>
  
  <p>Here are some samples (and this is <em>very</em> cut down... and yes, I use the "try/catch" to skip other functionality:</p>
  
  <p>Table looks like this:</p>
  
  <pre><code>CREATE TABLE [dbo].[test]
  (
      [id] [int] IDENTITY(1,1) NOT NULL,
      [value] [varchar](10) NOT NULL,
      [description] [varchar](100) NOT NULL,
      CONSTRAINT [PK_test_1] PRIMARY KEY CLUSTERED ([id] ASC) ON [PRIMARY]
  ) ON [PRIMARY]
  END
  GO
  CREATE UNIQUE NONCLUSTERED INDEX [UNQ_test_description] ON [dbo].[test]([description] ASC) ON [PRIMARY]
  GO
  </code></pre>
  
  <p>SP looks like this:</p>
  
  <pre><code>create procedure dbo.fred
  (
      @Code           varchar(10),
      @Description    varchar(100)
  )
  as
  begin
      begin try
  
          insert into dbo.test
              (value, [description])
          output
              inserted.ID
          values
              (@Code, @Description)
  
      end try
      begin catch
          throw
      end catch
  end
  </code></pre>