SQL Server 2008 once again comes with a TSQL Debugger (which is really quite good). The only condition for using it is that the code is available in a non-encrypted form on the database. To assure this, you have 2 options:
You can check if your code is decrypted by typing sp_helptext 'p_myproc' where the code of your procedure should appear in the results window.
The first time you start debugging a TSQL statement, Management studio pops up a request to alter your firewall settings to allow debugging. Unfortunately this did not work at all in my case. So I had to dive in and make the required changes.
A number of Firewall settings have to be done to enable TSQL Debugging on your pc.
Start the Firewall tool:
In the firewall application a number of changes need to be made:
Right click on the Inbound rules node and select 'New Rule...'
Then select 'Port' and click Next
Now enter the TCP Port 135 as excluded and click Next
In the next dialog you make sure 'Allow the Connection' is selected and click Next
On next pane you specify which networks can access this port. For security reasons it is best to disable "Public" networks. Select the options as shown below and click next.
In the Naming dialog enter something like "TSQL Remote Debugging: Allow DCOM" and click finish:
Again right click on the "Inbound rules" node and select New Rule. This time we take the option "Program" and click next.
In the next dialog we will select the path where sqlservr.exe is installed (This depends on the name of your SQL Server instance. You can search for sqlservr.exe in the "C:\Program Files" folder). Click next when Done.
Again select the "Allow the connection" option and click Next. In the next Dialog once again disable "Public" networks. Click next and enter as name "TSQL Remote Debugging: sqlservr.exe"
Follow the same process as for "Exclude TCP Port 135" but enter the ports "500, 4500" in the "Protocols and ports" dialog
Proceed the same as in "Exclude TCP Port 135" and enter "TSQL Remote Debugging: IpSec" as name
The next application to exclude is management studio. Follow the same approach as "Exclude sqlservr.exe" but search for ssms.exe (C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe) . Proceed identical as "Exclude sqlservr.exe" but enter "TSQL Remote Debugging: ssms.exe" as name
This one is a bit special because it exists in a 32-bit and 64-bit flavor. For convenience we will exclude them both.
Proceed the same as for sqlservr.exe and ssms.exe and exclude the following 2 applications
And the second rule
This concludes the firewall configuration required to perform TSQL Debugging.
Open management studio and enter the SQL Statement you wish to debug and click on the "Start Debugging" option under the "Debug" menu. (or press Alt-F5)
This will start your Query and stop processing on the first statement it encounters. This you can see from the yellow arrow in the left margin.
To continue we have a number of new options in the debug menu
When you meet a piece of Dynamic SQL to be executed the debugger will open a new window where you can step through the generated SQL Code (which is super nice)
To set an extra breakpoint (ie: to press on with Alt-F5 to a certain line of code you click in the margin of the code window or press F9 which will display a red dot. If you then hit Alt-F5 the execution will stop at this red dot as shown with the yellow arrow:
Another convenient way to "Catch up" to the current line is by right clicking and selecting the "Run to cursor" option. This will advance the debugger to your current line.
To help you debug your code there are a number of help windows available.
The first one is the "Locals" window, this window displays the content of the parameters and variables currently in the scope of the code.
The next dialog is the Quick Watch window. If you have a "complex" expression for which you want to see the results of an extract you can do this with a quick watch. Select the variables to evaluate and right click. Select the quick watch option which will open the following window where you can enter variables, parameters, and the system functions that have names that start with @@.
The TSQL debugger does not provide an "out of the box" way to view the content of "local" temp tables. For this I built a stored procedure which you can run from any thread. The code is available on https://github.com/FilipDeVos/sp_select.
Something you should not forget is that the output of your "Original" query is still being printed to the results window of the window where you started debugging. An easy layout to work with the debugger is by making a "Vertical Split" which enables you to see the output together with the debugged statements