It’s not that often that I need a debugger for my SQL code, but every once in a while it’s an absolute must, and today is one of those days. I have a several hundred line stored procedure which works with multiple view and table variables, dozens of variables, and three levels of nested cursors. And I am trying to find a sporadic logic problem. Simple stored procedure debugging can use embedded PRINT statements, but this one is more complex, and thus the need for a debugger.
But SQL Server 2005 removed the built-in debugger. Now you need to use Visual Studio .NET to debug SQL Server stored procedures. No problem, I happen to have a copy (which I use for occasional VB.NET and C# code). And sure enough, you can connect to SQL Server, open (and even edit) a stored procedure, set breakpoints, step through code, watch variables and expressions, everything you’d expect … unless what you want to inspect is anything but a simple variable. As already said, I am working with lots of table variables (actually, as I am working with lots of values at any given time just about everything I need to inspect is a table variable). And when you inspect those it displays “Table”. Gee, how useful!
The truth is that the old debugger had the same limitation, no visibility into temporary tables or table variables. But I was hoping that now that the debugger is actually Visual Studio that this limitation would be lifted, but nope.
If anyone knows of a solution or an ingenious workaround, I’d love to hear it. In the meantime, it’s back to embedded PRINT statements. Bummer.