preserving the stack trace when handling exceptions

i just now stumbled across something that i didn't realize (though it makes sense) about the difference between throw and throw ex within a try...catch...finally block and i thought i'd share the wealth. i have the feeling this may be one of the "holes" in my coding knowledge/fundamentals that everyone else knows except for me since i was a physics major and only took 16 hours worth of computer science back in college. if so, you can politely skip this post and be happy for me that i learned something valuable. if you think i'm dumb for not knowing this, just know that i think you're dumb for not knowing that an operator that commutes with the Hamiltonian does not evolve with time. yeah, what now??

here's the gist:
case i) if a method call within a try...catch block results in an exception, calling throw(with or without an exception instance) causes any code in the ...finally block to immediately execute and then execution continues on its jolly way up the call stack. if you want to log anything about the error, do so before calling throw. furthermore, calling throw by itself preserves the call stack, whereas calling throw with an exception instance causes the stack trace to restart from the method that re-throws the exception and you lose whatever precious debugging information you had about what was going on before the call.

case ii) if procedural code within a try...catch block results in an exception, you lose the stack trace whether you throw with an exception instance or without. you should log what you need to know about the error before you throw as suggested in case i) and additionally log the stack trace.

if you are truly masochistic/sadistic (not sure which but i'd lean towards the former), you can follow what some bloke by the name of Chris Taylor outlines here

selective filtering, part iii - dynamic sql filtering

i just thought of this as i posted my last entry and wanted to get it 'on paper' before it slips my mind.

if you are using dynamic sql to filter a query based on an optional/nullable parameter, you have to provide something to your WHERE clause in the case that you receive all nulls. here is an example that will not work using the clever ISNULL technique i discussed in my first post:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
SET @sSQL = @sSQL + 'WHERE col_b = ''' + ISNULL(@param1, 'col_b') + ''''

exec sp_executesql @sSQL
GO

what's the problem here? if @param1 is null, the WHERE clause is comparing the value of col_b to a constant, 'col_b', instead of setting it equal to itself. here is a correct way to accomplish the filter:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' WHERE col_b = ''' + @param1 + ''''

exec sp_executesql @sSQL
GO

in this case, you only filter if the @param1 contains a value. however, this once again presents a problem - what if there are multiple optional/nullable parameters provided? without knowing which ones contain values, you don't know where to put the WHERE. for example:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' WHERE col_b = ''' + @param1 + ''''
IF @param2 IS NOT NULL
SET @sSQL = @sSQL + ' WHERE col_c = ''' + @param2+ ''''

exec sp_executesql @sSQL
GO

this obviously breaks if both @param1 and @param2 are not null since you can't specify WHERE more than once. there are two options: write your query such that every combination of parameters containing and not containing values is accounted for (which is exponentially more difficult with each additional parameter), or you can be clever. let's rewrite the above sample:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
SET @sSQL = @sSQL + 'WHERE 1=1 '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' AND col_b = ''' + @param1 + ''''
IF @param2 IS NOT NULL
SET @sSQL = @sSQL + ' AND col_c = ''' + @param2+ ''''

exec sp_executesql @sSQL
GO

now that you've specified a condition that's always true (WHERE 1=1), you can dynamically add additional filters as needed. pretty damn slick if i do say so myself

one last thing - i always declare @sSQL as nvarchar(4000) in my examples because sp_executesql only works with variables with data types of nchar, ntext and nvarchar and the maximum size of an nvarchar variable used with sp_executesql is 4000

selective filtering, part ii - dynamic sql sorting 1

my previous post on selective filtering dealt with filtering a select statement if a non-null parameter value is passed to your stored procedure without using dynamic sql. however, there will be times when dynamic sql is required. since the 'sp_executesql' stored procedure was included in SQL 2005 and was not removed in SQL 2008 (to my best knowledge), i dare anyone to dispute that (but if you do so successfully i will be simultaneously amazed, humbled and eternally grateful).

for the uninitiated, dynamic sql is the process of building a query on-the-fly based on parameters passed to the stored procedure. an example of when this is required is if your query accepts the name of a column as a parameter (@param1) and you want to sort your query based on this column/parameter. you can't order a query by a constant, null, or a parameter, even if the parameter evaluates to a valid column name. instead, you write something like this:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' ORDER BY ' + @param1

exec sp_executesql @sSQL
GO

assuming @param1 is a valid column name in table_a, the statement will run and return the values of col_a ordered by the @param1 column (in ascending order by default). notice that i checked to make sure @param1 contained some value since you can't order by null and, if i hadn't checked and @param1 was null, @sSQL would be null (since anyting + null = null). sp_executesql won't throw an error if @sSQL is null; it will just say "Command(s) completed successfully." and return nothing.

that does it for the basics of dynamic sql sorting. the reason i named this post 'dynamic sql sorting 1' is because i can think of at least one more complicated case (e.g. using a case statement to select a column to sort by when another column's value is equal to an input parameter) that i don't want to go into right now since this post is already pretty long. good luck and by all means let me know if you have any comments, corrections or questions. später

thread was being aborted

can it be that this demi-god of t-sql can also code and happens to know a thing or two about debugging? holy shit, how has some beautiful lady not yet scooped him up and made him hers by constantly giving him hot, hot lovin? i ask myself these questions often. i also ask myself who the hell thinks that choking a dude is a good way of saying 'nicely done', but that's a story for another time...

if you are using response.redirect(url) or server.transfer in your web application and receiving a "Thread was being aborted" error despite the fact that your application does absoluetely nothing explicitly with threading, you may be as confused as i was when this happened to me. i'll be the first to admit that, despite all those lovely Microsoft certification letters after my name, i'm far from an expert on page life cycles and the intricacies of what goes on behind the scenes when the application does something as seemingly simple as changing the page that is displayed to a user. here is what i found:

response.redirect and server.transfer both internally call response.end. response.end ends the current page execution, the Application_EndRequest event is fired and a ThreadAbortException is thrown (i'm unsure whether or not resonse.end throws the ThreadAbortException to immediately shift the page life cycle to Application_EndRequest of if the exception is thrown in the Application_EndRequest event). that code which follows response.end (and, therefore, response.redirect and server.transfer) is not executed.

you: "why not just wrap the page redirection in a try...catch block and catch the exception?"

a ThreadAbortException will be rethrown at the end of your catch block whether you like it or not. it will then execute everything in your ...finally block and then kill your current thread. if you feel like dealing with threads, you can call thread.resetabort in your catch block to continue executing your thread, but this discussion assumes you are not dealing with threading, so scratch that.

solution: it turns out this is actually a pretty easy fix. if you are using response.redirect(url), use the overloaded version response.redirect(url, false) instead. if you are using server.transfer, use server.execute.

works cited:
Microsft Help and Support
DotNetJunkies - In the line of Fire -- Shaunak Pandit
Microsoft Visual Studio 2005 Documentation - ThreadAbortClass

deadlocks are bad

what is a deadlock? the way i typically explain it is like so (this is simplified but i think it gets the idea across):

• process A has a lock on table A and is waiting to update it with information from table B
• process B has a lock on table B and is waiting to update it with information from table A

since neither process will release its lock until the other processes releases its lock, we have ourselves a deadlock. most of the time SQL server "resolves" this issue itself because it has a thread dedicated to its lock manager and someone much smarter than myself came up with an algorithm that allows the lock manager to detect this and kill one of the processes. i have no clue how it chooses which process is the victim of its kill statement, but, from my experience, 99% of the time it is does make this decision and it does kill one of the processes. i've not yet had this happen in SQL 2005, but i can count on one hand the number of times when a deadlock occurred in SQL 2000 and SQL left the decision up to me. in all of these cases, the decision for me was simple - i opened up the SQL activity monitor, scrolled to the right, and saw that a whole bunch of locked processes listed the same process in the 'Blocked By' column. by no means should you be cavalier and just kill this single process before you know what it is, but it's a good place to start. i figured out what the process was and had my client log on to the server hosting the application and simply close it.

back to the other 99% of the time SQL Server resolved the deadlock automagically. this is still very bad. some application was trying to perform some operation on a table or its data and SQL Server flat out squashed it before it could finish. there are certainly ways design a database to minimize the chance of this, but no matter how badass you think your 5NF database is (because there is no way you are badass enough to reach 6NF), you are going to experience a deadlock at some point. i recommend you read the following article to better understand what is going on, and how to track down resolve the problem:

http://support.microsoft.com/kb/832524

selective filtering, part i

what if you only want to filter a select statement if a non-null value is passed to your query as a parameter? you have a couple of options. one is to use dynamic sql to build your WHERE clause and only include the filter if the parameter value is not null. however, as i will attempt to establish as law at one point or another, dynamic sql is bad and should be avoided at all costs. from my experience, the easiest and best way to accomplish this without resorting to dynamic sql is to make use of the ISNULL function like so:

WHERE ...
AND a.column_a = ISNULL(@param1, a.column_a)

a.column_a = a.column_a is always true if a.column_a does not contain nulls, thus if a null is passed to @param1, the statement will not be filtered. if the column can contain nulls, we have to be a be more creative depending on what you want to accomplish. what about using the SET ANSI_NULLS OFF option? this allows for the logical comparison of nulls (i.e. null = null evaluatues to true). doing so makes the example above return all rows when @param1 is null. if you do not want rows returned where a.column_a is null, simply add an additional line:

WHERE ...
AND a.column_a = ISNULL(@param1, a.column_a)
AND a.column_a IS NOT NULL

problem solved. one last thing i should note is my example is likely a much simpler example than what you have since i am only concerned with one column; you must take caution to make sure your other comparisons will not return undesired results when using SET ANSI_NULLS OFF.