title: How to rethrow the same exception in SQL Server
body: <p>I want to rethrow the same exception in SQL Server that has just occurred in my try block. I am able to throw same message but I want to throw same error.</p>
  <pre><code>BEGIN TRANSACTION
      BEGIN TRY
          INSERT INTO Tags.tblDomain (DomainName, SubDomainId, DomainCode, Description)
              VALUES(@DomainName, @SubDomainId, @DomainCode, @Description)
          COMMIT TRANSACTION
      END TRY
      
      BEGIN CATCH
          declare @severity int; 
          declare @state int;
  
          select @severity=error_severity(), @state=error_state();
  
          RAISERROR(@@Error,@ErrorSeverity,@state);
          ROLLBACK TRANSACTION
      END CATCH
  </code></pre>
  <blockquote>
  <p><code>RAISERROR(@@Error, @ErrorSeverity, @state);</code></p>
  </blockquote>
  <p>This line will show error, but I want functionality something like that.
  This raises error with error number 50000, but I want the error number to be thrown that I am passing <code>@@error</code>,</p>
  <p>I want to capture this error no at the frontend.</p>
  <p>i.e.</p>
  <pre><code>catch (SqlException ex)
  {
      if ex.number==2627
      MessageBox.show(&quot;Duplicate value cannot be inserted&quot;);
  }
  </code></pre>
  <p>I want this functionality. which can't be achieved using <code>raiseerror</code>. I don't want to give custom error message at back end.</p>
  <p><code>RAISEERROR</code> should return below mentioned error when I pass ErrorNo to be thrown in catch</p>
  <blockquote>
  <pre><code>Msg 2627, Level 14, State 1, Procedure spOTest_DomainInsert,
  </code></pre>
  <p>Line 14
  Violation of UNIQUE KEY constraint 'UK_DomainCode'. Cannot insert
  duplicate key in object
  'Tags.tblDomain'.
  The statement has been terminated.</p>
  </blockquote>
  <p><strong>EDIT:</strong></p>
  <p>What can be the drawback of not using try catch block if I want exception to be handled at frontend considering stored procedure contains multiple queries that need to be executed?</p>