Try catch in a SP

• VRT

  SSCertifiable

  Points: 5776

  More actions

  March 5, 2009 at 2:20 am

  #128781

  Hi,

  can any one tell me why the below error coming in the following code?

  BEGIN CATCH

  INSERT INTO [dbo].[tab1_Failures]

  (DateofError,

  ErrorNum,

  ErrorDesc,

  ErrLine,

  ErrorProc

  )

  SELECT

  GETDATE() AS DateofError,

  ERROR_NUMBER() AS ErrorNumber,

  ERROR_MESSAGE() as ErrorMessage;

  ERROR_LINE() as ErrorLine,

  ERROR_PROCEDURE() as ErrorProcedure;

  END CATCH

  The error is as follows...

  Msg 120, Level 15, State 1, Procedure sp_UnitConfiguration_insert, Line 130

  The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

  Thanks for you great help,

  Venki.

  Thank You

• VRT

  SSCertifiable

  Points: 5776

  More actions

  March 5, 2009 at 2:31 am

  #953859

  OK.... sorry....

  I found the problem.

  There is an extra ;(semicolon) came in the line ERROR_MESSAGE()

  It looks simple but I struggled to find out.

  Thanks for your help,

  Venki.

  Thank You

• NitinPatel31

  Hall of Fame

  Points: 3721

  More actions

  March 5, 2009 at 3:55 am

  #953888

  Replace semi-comma(;) with comma(,) in below line (third line of SELECT statement).

  ERROR_MESSAGE() as ErrorMessage;

  Regards,
  Nitin

• VRT

  SSCertifiable

  Points: 5776

  More actions

  March 5, 2009 at 4:09 am

  #953897

  Hi,

  Thanks for you reply.

  But I didn't get what I want in the above code.

  I want to insert the errors into a error_log table when the insertion to a table fails. So I used try catch but it was not throwing that the package failed and it was simply showing successful.

  I want that package failed as well as the error message to be inserted into the error_log table.

  Please tell me how can I do that?

  Thank You

• NitinPatel31

  Hall of Fame

  Points: 3721

  More actions

  March 5, 2009 at 4:30 am

  #953905

  Use RAISERROR statment inside CATCH block.

  Regards,
  Nitin

• VRT

  SSCertifiable

  Points: 5776

  More actions

  March 5, 2009 at 5:14 am

  #953922

  nitinpatel31 (3/5/2009)

  ---

  Use RAISERROR statment inside CATCH block.

  How could I add the RAISEERROR stmt in the above CATCH block.

  If I add it before END CATCH stmt, it is showing the error___

  Msg 102, Level 15, State 1, Procedure sp_UnitConfiguration_insert_New, Line 146

  Incorrect syntax near 'RAISEERROR'.

  again code is here....

  BEGIN CATCH

  INSERT INTO [dbo].[ESStoCSInquiry_Failures]

  (

  DateofError,

  ErrorNum,

  ErrorDesc,

  ErrorLine,

  ErrorProc

  )

  SELECT

  GETDATE() AS DateofError,

  ERROR_NUMBER() AS ErrorNumber,

  ERROR_MESSAGE() as ErrorMessage,

  ERROR_LINE() as ErrorLine,

  ERROR_PROCEDURE() as ErrorProcedure;

  RAISEERROR(ERROR_NUMBER(),16,1)

  END CATCH

  Thank You

• NitinPatel31

  Hall of Fame

  Points: 3721

  More actions

  March 5, 2009 at 5:51 am

  #953938

  It has some errors.

  - It is RAISERROR not RAISEERROR.

  - Function can be passed as argument.

  Use below code for it

  DECLARE @Error VARCHAR(150)

  SET @Error = ERROR_MESSAGE()

  RAISERROR (@Error,16,1)

  Regards,
  Nitin

• VRT

  SSCertifiable

  Points: 5776

  More actions

  March 5, 2009 at 7:42 am

  #954032

  nitinpatel31 (3/5/2009)

  ---

  It has some errors.

  - It is RAISERROR not RAISEERROR.

  - Function can be passed as argument.

  Use below code for it

  DECLARE @Error VARCHAR(150)

  SET @Error = ERROR_MESSAGE()

  RAISERROR (@Error,16,1)

  Thanks for your help. Now it is working fine.

  Thank You