title: SQL Server Stored Procedure Error on Insert using TRY CATCH
body: <p>I wrote a stored procedure which uses encryption algorithm to insert a row into a  table. The procedure is executing successfully, but no row is added. </p>
  
  <p>My code:</p>
  
  <pre><code>CREATE TABLE dbo.[User]
  (
      UserID INT IDENTITY(1,1) NOT NULL,
      Email NVARCHAR(64) NOT NULL,
      PasswordHash BINARY(64) NOT NULL,
      FirstName NVARCHAR(40) NULL,
      LastName NVARCHAR(40) NULL,
  
      CONSTRAINT [PK_User_UserID] PRIMARY KEY CLUSTERED (UserID ASC)
  )
  
  CREATE PROCEDURE dbo.addUser
      @pEmail NVARCHAR(64), 
      @pPassword NVARCHAR(64), 
      @pFirstName NVARCHAR(40) = NULL, 
      @pLastName NVARCHAR(40) = NULL,
      @responseMessage NVARCHAR(250) OUTPUT
  AS
  BEGIN
      BEGIN TRY
          INSERT INTO dbo.[User] (Email, PasswordHash, FirstName, LastName)
          VALUES(@pEmail, HASHBYTES('SHA2_512', @pPassword), @pFirstName, @pLastName)
  
          SET @responseMessage = 'Success'
      END TRY
      BEGIN CATCH
          SET @responseMessage = ERROR_MESSAGE() 
      END CATCH
  END
  
  DECLARE @responseMessage NVARCHAR(250)
  EXEC dbo.addUser
            @pEmail = 'Admin',
            @pPassword = '123',
            @pFirstName = 'Admin',
            @pLastName = 'Administrator',
            @responseMessage=@responseMessage OUTPUT
  </code></pre>
  
  <p>After executing the procedure, no rows have been added to the table.</p>