Why runtime Errors not get handled of #table in SQL server?

• sawantpl@gmail.com

  Old Hand

  Points: 326

  More actions

  September 23, 2010 at 9:47 am

  #226752

  In below stored procedure control doesnt go inside IF @@ERROR <> 0.

  Please let me know the reasons? Please suggest any error handling mechanism that will solve my problem.

  CREATE PROC sp_Process

  AS

  BEGIN

  CREATE TABLE #mytbl(

  n_ID INT NOT NULL

  )

  --Runtime error created

  INSERT INTO #mytb(n_ID_D)

  SELECT 1

  IF @@ERROR <> 0

  BEGIN

  -- control should come here & say Error.

  SELECT 'ERROR'

  END

  END

  GO

• Lowell

  SSC Guru

  Points: 323518

  More actions

  September 23, 2010 at 10:47 am

  #1226459

  i think that without a TRY CATCH, the moment an error is encountered, processing stops, and it never gets to the error handling code;

  i've got this example saved in my snippets as a more robust example of TRY CATCH: maybe you can use it as your model?

  BEGIN TRY

  Begin Transaction

  DELETE FROM #TABLE1 WHERE customerid = @customerid

  DELETE FROM #TABLE2 WHERE customerid = @customerid

  DELETE FROM #TABLE3 WHERE customerid = @customerid

  Commit transaction

  END TRY

  BEGIN CATCH

  DECLARE @ErrorSeverity INT, @ErrorNumber INT,

  @ErrorMessage NVARCHAR(4000), @ErrorState INT

  SET @ErrorSeverity = ERROR_SEVERITY()

  SET @ErrorNumber = ERROR_NUMBER()

  SET @ErrorMessage = ERROR_MESSAGE()

  SET @ErrorState = ERROR_STATE()

  IF @ErrorState = 0

  SET @ErrorState = 1

  RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity,

  @ErrorState, @ErrorNumber)

  IF XACT_STATE() < 0

  ROLLBACK TRANSACTION

  END CATCH

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• winash

  SSChampion

  Points: 11446

  More actions

  September 23, 2010 at 12:46 pm

  #1226539

  The behaviour of @@ERROR (and TRY...CATCH) depends on the scope of the error (check http://www.sommarskog.se/error-handling-I.html#whathappens)

  For example:

  BEGIN TRY

  -- statement termination error

  SELECT 1/0

  END TRY

  BEGIN CATCH

  DECLARE @ErrMsg nvarchar(2048)

  SET @ErrMsg = ERROR_MESSAGE()

  SELECT @ErrMsg

  END CATCH

  GO

  BEGIN TRY

  -- batch termination error - cannot do anything in the CATCH block

  SELECT ThisColumnDoesNotExist FROM sys.columns

  END TRY

  BEGIN CATCH

  DECLARE @ErrMsg nvarchar(2048)

  SET @ErrMsg = ERROR_MESSAGE()

  SELECT @ErrMsg

  END CATCH

  GO

  -- same thing with @@ERROR

  -- statement termination error

  SELECT 1/0

  IF @@ERROR<>0

  BEGIN

  -- comes here and does this and then errors out

  SELECT 'Error!!'

  END

  GO

  -- batch termination error

  SELECT ThisColumnDoesNotExist FROM sys.columns

  IF @@ERROR<>0

  BEGIN

  -- never comes here

  SELECT 'Error!!'

  END

  SELECT @@ERROR AS '@@ERROR'

  GO

  -- in the next batch you can get the error number of the earlier batch termination error

  SELECT @@ERROR AS '@@ERROR Next Batch'

  If you want to consistently handle this behaviour it will depend on what kind of situations you want to handle...one other option would be something like this http://www.sqlservercentral.com/Forums/Topic987849-338-1.aspx#bm988474