The question I get asked is much higher-level than that. it is more like 'How do you actually go about writing a stored procedure'. I always tend to start out on the same tack as you, but I soon find I'm not answering the question. It is more like how you go about debugging code, changing sections of it, refactoring it, or just plain and simply how you check on the behavior of the system without having breakpoints or sophisticated debugging.

You have developed a stored procedure, maybe, and you want to check on the parameters passed to it, or perhaps the value of a variable, or the contents of a temporary table when the system is actually running. How would you construct a test harness for it? How do you go about profiling the performance of a stored procedure to find where the performance problems are?

What sort of advice would you give?