Critical Error Trapping

• our spt

  SSC Veteran

  Points: 234

  More actions

  March 23, 2005 at 11:57 pm

  #76265

  Hi,

  I have a situation where I need to give a custom error message in an SP where I get a primary key violation. I tried this with the @@error variable as in the following code.

  CREATE PROCEDURE TESTspt1PROC

  AS

  insert into testspt1 values (1, 'spt')

  if @@error <> 0

  PRINT 'it is a Primary Key error and ..'

  There is already a record in testspt1 table with the value '1'. When I execute the procedure, I get the following output:

  Violation of PRIMARY KEY constraint 'PK__TestRaj1__7D2E8C24'. Cannot insert duplicate key in object 'TestRaj1'.

  The statement has been terminated.

  'it is a Primary Key error and ..'

  i.e, it is giving the SQL Server standard message first and then giving the custom message I gave. I need to get only the custom message and not the SQL Server standard message in addtion.

  Is there any way to make it possible?

  Thanks in advance

  ourspt

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 24, 2005 at 1:12 am

  #548666

  Here's a pretty good overview on error handling in SQL Server:

  http://www.sommarskog.se/error-handling-II.html

  http://www.sommarskog.se/error-handling-I.html

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]