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:
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.