SELECT interferes with RAISERROR in CATCH

• George Rice-394895

  SSC Rookie

  Points: 29

  More actions

  April 22, 2009 at 4:17 pm

  #982594

  Regarging GSquared:

  If you run the script exactly as you have it, the RAISERROR works because you're doing a variable assignment, not a select that returns a resultset.

  If you uncomment the actual SELECT statement (select anything you want), the RAISERROR at the end of the t-sql catch block does not make it back, at least to a C# catch construct.

  It seems that a SELECT (the kind that returns a resultset) inside of a CATCH just "outputs" the resultset and then stops.

  -George

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 23, 2009 at 6:49 am

  #982817

  Florian Reischl (4/22/2009)

  ---

  Hi Gus (hope I am allowed to call you Gus)

  I just tried again and I think I figured out the problem. It depends on the C# code. As long as I use a DataTable to be filled .Net ends after the first returned data (the SELECT). When I use a DataSet which is made to handle more than one result set I get all data, the PRINT messages and the RAISERROR, if defined.

  Greets

  Flo

  Anyone who wants to may call me Gus. 🙂

  All I tested was calling and catching the error. Didn't try to do anything with any data. So I guess it's no surprise that it worked differently for me. Glad you figured it out.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• George Rice-394895

  SSC Rookie

  Points: 29

  More actions

  April 23, 2009 at 8:27 am

  #982952

  One thing I haven't tested is, at the end of the CATCH block, to have code that looks something like this called from within a C# try construct. I now know for a fact, at least in my case, the RAISERROR has no effect after SELECTing 'xyz' as a resultset, but it would be somewhat disturbing if the ROLLBACK also fails. If this fails to be caught by my C# catch, I'll have to revisit all the SQL I've written in the last two years. I'll post my result when I can get around to it:

  BEGIN CATCH

  DECLARE @ErrorSeverity INT,

  @ErrorNumber INT,

  @ErrorMessage NVARCHAR(4000),

  @ErrorState INT,

  @ErrorProcedure NVARCHAR(100)

  SELECT @ErrorSeverity = ERROR_SEVERITY(),

  @ErrorNumber = ERROR_NUMBER(),

  @ErrorMessage = ERROR_MESSAGE(),

  @ErrorState = ERROR_STATE(),

  @ErrorProcedure = ERROR_PROCEDURE()

  SELECT 'xyz' -- somewhat unlikely scenario, but who knows?

  ROLLBACK

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

  END CATCH