Error types

• bcronce

  SSCrazy

  Points: 2597

  More actions

  February 17, 2011 at 8:19 am

  #234558

  I have a large SQL import and transformation process that runs and I'm having an issue with some error types.

  I have two common types of errors, conversion and sub-queries. Seems conversion errors take down the entire process and sub-queries error and continue on. Why is there this difference?

  Here's an example

  select *

  from tblTable

  where icustomerid = (select icustomerid from tblTable)

  print '1'

  select *

  from tblTable

  where icustomerid = 'a'

  print '2'

  and I get

  Msg 512, Level 16, State 1, Line 1

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

  1

  Msg 245, Level 16, State 1, Line 9

  Conversion failed when converting the varchar value 'a' to data type int.

  I see the first print statement because SQL doesn't care about that error, but it does seem to care about the conversion error. They both have the same error level, but they are treated differently.:crazy:

  Is there a list somewhere of errors that cause SQL to kill the spid and ones that don't?

  Thanks 🙂

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  February 17, 2011 at 3:13 pm

  #1288551

  An interesting question. I would suggest that using try-catch would be better regardless of the outcome of the unhandled exception being thrown. Handle the exception, log it so you know it happened, send an email if you want, move on. 😉

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/