Error messages

• Kayal-717623

  Ten Centuries

  Points: 1390

  More actions

  July 21, 2008 at 4:40 pm

  #99197

  Hi All,

  Is there any way that i can interpret the error messages from the sql server to a friendly error message and send it the front end application?

  I read about error_message but i guess it can be used only inside try-catch block.

  Thanks in advance.

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  July 22, 2008 at 5:57 am

  #846321

  You've got two options. Set up TRY/CATCH and then return the error message that you want the user to see:

  BEGIN TRY

  RAISERROR('Major error in TRY block.',16,1);

  END TRY

  BEGIN CATCH

  DECLARE @ErrorMessage NVARCHAR(4000),

  @ErrorSeverity INT,

  @ErrorState INT;

  -- You can use ERROR_MESSAGE()

  SET @ErrorMessage = ERROR_MESSAGE();

  -- Or you can use your own

  SET @ErrorMessage = 'Game over, man. Game over.'

  SET @ErrorSeverity = ERROR_SEVERITY();

  SET @ErrorState = ERROR_STATE();

  RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);

  END CATCH;

  Or you can catch the error on the client side in the code and translate it to a prettier message for the user.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning