title: Return error message from stored procedure
body: <p>The question should be quite simple, but I can't figure out the answer nor why my stored procedure is not working.</p>
  
  <pre><code>CREATE PROCEDURE spTest_Delete
  @ID int
  AS
      begin tran
          declare @err int
          declare @errMesage nvarchar(max)
          set @errMesage = ''
          set @err = 0
  
          delete from Test
          where ID = @ID
  
          set @err = @@ERROR
          set @errMesage = ERROR_MESSAGE()
  
          if @err = 0 
             commit tran
          else 
          begin
              RAISERROR(N'Could not delete !Error nr: %d. Message: %s', 1, 16, @err, @errMesage)
              rollback tran
          end
  </code></pre>
  
  <p>This procedure runs ok, but in case of FK constraint on the <code>delete</code> statement it runs into an error (which is good) and I would like to catch the error. </p>
  
  <blockquote>
    <p>Msg 547, Level 16, State 0, Procedure spTest_Delete, Line 12<br>
    The DELETE statement conflicted with the REFERENCE constraint
    "FK_TEstFK_Test". The conflict occurred in database "Test", table
    "dbo.Test", column 'ID'. The statement has been terminated.   </p>
    
    <p>Could not delete!<br>
    Error nr: 547. Message: (null) Msg 50000, Level 1, State 16</p>
  </blockquote>
  
  <p>I always get null for my message variable, even though the <code>delete</code> statement throws an error. </p>