What is the best way to debug stored procedures (and write sprocs that are easier to debug)?

Question

What are good methodologies for creating sprocs that reduce the pain of debugging? And what tools are out there for debugging stored procedures?

Perhaps most importantly, what are indications to look out for that errors are happening in a sproc and not in the code? I hope I'm not all over the board too terribly bad here. Votes for answers to any of the above. Thanks.

For what it's worth, I work in a .NET environment, SQL servers.

Answer 1

One technique I use in stored procedures to make them easier to debug (without IDE or debuggers) for SQL Server 2005 procedures:

I add an input parameter named @Debug = 0 (defaulted to 0 = off) at the end of the parameter list for the procedure.

I then add if (@Debug = 1) print '...';

statements in the code at key junctures to display any useful internal values etc.

Yes, it's "old school" and debuggers that let you "walk the code" are great - but this works for anyone from any SQL tool (including anyone debugging without your same IDE).

Ron