For those of us who develop against SQL Server, being able to work with stored procedures is an important productivity benefit. In pre-2005 releases of Visual Basic and Visual Studio, developers have to be pretty creative to get T-SQL debugging to work at all. No more; it's fixed. Not only does debugging work, but Microsoft has added exciting features that should make it even easier to debug and tune the most complex stored procedures. In Visual Studio 2005, you can now set the breakpoint to fire:

  • On the nth hit—In this case you can choose to break when the hit count is equal to, greater than or equal to, or a multiple of a given value. This means you can break when you know that the stored procedure needs to be watched after it has been called n times.
  • When a filter condition is met—You can restrict the breakpoint to fire only when specified processes or threads invoke the stored procedure. You can specify MachineName, ProcessID, ProcessName, ThreadID, and ThreadName by using the AND (&), OR (||), or NOT (!) characters in an expression. For example, if SQL Server is running on a server farm, you can debug the stored procedure when it runs on a specific server in the farm.

Apparently, SQL Server 2005 doesn't support conditional SQL Server breakpoints, so you won't be able to choose this option. Another option lets you specify that a message be displayed in the debug window when the breakpoint is hit, as Figure A shows.

Enabling T-SQL debugging is also easier in Visual Studio 2005 than in previous releases, but the feature is disabled in the Standard Edition, so you'll need the Professional Edition or Team System Edition to use this feature.

To debug a specific stored procedure, use the Server Explorer to open your stored procedure and navigate to the line at which you want to break execution, just as you do in Visual Studio 2003. To set a breakpoint, right-click in the margin just as you would for normal code in the stored procedure code editor. Next, open the Application Properties window (which is new in Visual Studio 2005), choose the Debug tab and select Enable SQL Server debugging. You don't have to attach to a process to get Visual Studio to break execution as you had to in previous betas. When you test your application, Visual Studio exposes an interactive T-SQL editor window after you execute your stored procedure and the breakpoint conditions are met. Cool.