When we hire new employees (at my day job) we start with a phone interview.

I've got a list of a couple dozen questions we run through that give us an idea

of whether we should consider bringing the person in for a more in depth

interview. While a discussion of those questions is probably worth an article in

itself, we'll stick closer to SQL today and talk about just one of the questions

I ask of candidates for senior developer positions.

Here it is:

"A junior developer comes to you with the following problem. They have a SQL

stored procedure comprised of about 200 lines of business logic. The procedure

executes without error but does not return the expected results. Without looking

at the code, what guidance would you offer that would help them solve the

problem?"

Think on it for a minute, I imagine you'll think of a couple different

options and maybe a couple questions. What do you think is the common answer?

Answer: Add print statements to the code.

Other answers I've been given:

• You shouldn't have that much code in a single stored procedure
• I'd have to look at the code
• Have them check the data

In 10% of the interviews they'll suggest using a debugger, either the one in

Query Analyzer or the one in Visual Studio.Net.

So let's talk about what I think about the answers briefly. Adding print

statements isn't the worst idea, similar to adding debug.print or

debug.writeline statements or whatever your language of choice supports. What I

look for is do they go the extra mile, suggest putting one inside every branch,

or putting one halfway to cut in half the lines of code to inspect. Too much

code in one proc? Hard to tell based just on lines of code, but I don't see 200

as excessive - still, I'm open to a discussion on that point. Looking at the

code? Sure you want to look, but I'm looking for someone who doesn't HAVE to

look, they can explain how they solve the problem to someone else in repeatable

fashion. Checking the data? Definitely part of the process, bad data happens far

too often, always a factor to be considered.

Possibly you can tell I'm a fan of the debugger approach. Why? Suppose we

reframed the question to look something like this:

"A junior developer comes to you with the following problem. They have a VB6/VB.Net

method comprised of about 200 lines of business logic. The method executes

without error but does not return the expected results. Without looking at the

code, what guidance would you offer that would help them solve the problem?"

I'd be willing to bet that almost every developer will say that you should

step through the code. Why does the answer change? Developers think of VB6/VB.Net/C#/whatever

as code, but stored procedures as magical things on the server. If you're

using VB6 or whatever you have a very rich development environment - step

through, watch variables, etc, but for some reason developers don't think of

stored procedure code as code. Even when they make that leap, few stop to

realize that they are debugging stored procedures with techniques worthy of the

early nineties.

As far as the interview itself, this question let's me see if they can see

outside their world a little, see how the react to a small challenge, how would

they do as a mentor? Standard stuff I think. While you you might not agree with

the question, I hope you'll think about the range of answers and why so few

developers use a debugger on stored procedures.