When does SQL SELECT statements throw exceptions?

Question

TSQL here. Specifically Server 2008(literally just upgraded)

Concerning stored procedures: Try/Catch

I was trying to make a list of cases when a Select Statement will throw an exception. The ones I can think of are syntax related(includes null variables) and divide by zero. I'm only guessing there are just a whole boat load of them for Insert/Alter and Create/Truncate.

If you happen to know of a good source link, that would be great.

This question came up when I was reading this exhaustive blog post about error handling for SQL server. It's titled for SQL Server 2000, but I think most of it still applies.

edit

Sorry, I meant to link this earlier. . .

http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspx

It might be nice to include a link to the exhaustive blog post. — Damien_The_Unbeliever, Jul 19 '11 at 17:13
You won't have read about TRY/CATCH in SQL Server 2000. It was introduced with SQL Server 2005... And do you mean http://www.sommarskog.se/error_handling_2005.html ? — gbn, Jul 19 '11 at 19:55
Yeah, I forgot to include that link. @gbn: Yeah, the blog post I'm linking has a bunch of if/elses. I was going to reconcile that with try/catch. Mainly, I don't want to catch exceptions I'm not willing to handle. — surfasb, Jul 19 '11 at 20:16

score 1 · Accepted Answer · edited May 23 '17 at 11:55

1

Outside for compile ("didnt' run") errors, you have at least these runtime errors

arithmetic errors
These change based on various SET statement
Example: get sql server to warn about truncation / rounding
overflow errors
example: one of the rows overflows smallint in some calculation
CAST errors
eg you try ISNUMERIC in a WHERE or CASE and try to cast 'bob^' or 1.23 to int
See Why use Select Top 100 Percent?

However, you'd always want to use TRY/CATCH though, surely...?

edited May 23 '17 at 11:55

Community

1
1

answered Jul 19 '11 at 16:40

gbn

422,506
82
585
676

That link was real good. The syntax and sematics of SQL/stored procedures aren't too complicated, so I'm going to put some time in learning proper error handling. – surfasb Jul 20 '11 at 00:20
@surfasb: see my answer here please then. You can remove the transaction stuff for read only procs. http://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern/2074139#2074139 also, most folk don't tend to catch individual errors. It fails or it runs. Unless you need patterns like this: http://stackoverflow.com/questions/3593870/select-insert-version-of-an-upsert-is-there-a-design-pattern-for-high-concurre/3594328#3594328 (nested try/catch) – gbn Jul 20 '11 at 05:27

score 1 · Answer 2 · answered Jul 19 '11 at 18:19

Adding to gbn's post, you can also get locking errors like lock wait timeouts and deadlocks.

If you are referencing #Temp tables, you can get "Invalid object name '#Temp'" errors, because these are unbound until the statement executes.

If you are in READ UNCOMMITTED or WITH (NOLOCK), you can get error: 601 - "Could not continue scan with NOLOCK due to data movement."

If your code runs .NET code, you would probably get exceptions from there.

If your code selects from a remote server, you could a whole different set of errors about connections.

If you do **anything** over a remote connection, then all bets are off. . . — surfasb, Jul 20 '11 at 00:18

When does SQL SELECT statements throw exceptions?

2 Answers2