Forta.com - Blog

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.

Comments

[Add Comment]

You may want to try creating a watch, when debugging in C#, the only way to see the value of something complex is with a watch, so perhaps a watch of tmptable.value?

# Posted By David | 12/5/06 12:20 AM

A side note about writing stored procedures in SQL Server 2005. You are better off writting your stored procedures in T-SQL unless you are doing something mathematically intensive. SQL Server will execute the procedure quicker if you write them in Transact SQL. This is the official response from Microsoft when they are asked about this question.

Still, it is nice to have the debugging tools.

# Posted By David Fekke | 12/5/06 8:58 AM

Would you mind posting some (or all) of your proc? I am sure that we may be able to assist you with this.

# Posted By David L. Penton | 12/5/06 5:11 PM

Just wanted to let you know that an invalid character in this post is causing the rss feed to choke:

If anyone knows of a solution or an ingenious workaround, I'd love to hear it. In the meantime, its back to embedded PRINT statements. Bummer.

# Posted By David Betz | 12/6/06 10:20 AM

Ugh, hate when that happens. Thanks David, I've fixed it.

--- Ben

# Posted By Ben Forta | 12/6/06 10:42 AM

Wow 3 levels of <evil> cursors </evil>
Are you sure you can't convert this to a SET based solution
Anyway create a couple of real tables dump the table variables into those tables while debugging look at those tables
You might have to specify the NOLOCK hint since they might be locked because of the transaction

Denis

# Posted By Denis The SQL Menace | 12/6/06 1:46 PM

Hey, cursors do have a use. :-) I'm looking at all options, but ... there really are three levels of nested logic, and rules that need to be processed for each and passed up the line, cursors are the best option I have thus far. But, I am still looking at all options.

--- Ben

# Posted By Ben Forta | 12/6/06 1:51 PM

Depending on your design what about 3 staging (processing tables)
process first batch in table 1
then use the info in that table and use that to update table 2
once you are done with all join the last table to the real table and update all the changes

But like I said i don't know your design and it's difficult to give a precise answer

Denis

# Posted By Denis The SQL Menace | 12/6/06 1:56 PM

[Add Comment]

SQL Server 2005 Debugging Woes