TRY/CATCH and RAISERROR blocks

• redmittens7

  SSChasing Mays

  Points: 628

  More actions

  May 18, 2015 at 9:08 pm

  #318236

  Hello,

  I'm trying to modify the following command with TRY/CATCH blocks. When the department already exists, it should return an error with the RAISERROR statement. To do this, how do I create the error message first?

  DECLARE

  @DeptName varchar(100) = 'NewDepartment'

  ,@DeptGroup varchar(100) = 'NewGroup'

  INSERT INTO [HumanResources].[Department] ([Name],[GroupName],[ModifiedDate])

  VALUES (@DeptName, @DeptGroup, GETDATE());

  Thanks in advance,

  RM

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  May 19, 2015 at 1:05 am

  #1798905

  Raiserror can work with a string or an error number. If you use a string, the error number will be 50000. Here is a small example:

  RAISERROR ('This is an example',16,1)

  If you want to add an error message to SQL server and use its number, you can use the procedure sp_addmessage. Here is another example:

  EXEC sp_addmessage @msgnum = 50002,

  @severity = 16,

  @msgtext = N'This is an example'

  go

  RAISERROR(50002,16,1)

  go

  --cleanup

  EXEC sp_dropmessage 50002

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/