Rotating Header Image

TSQL Debugging In SQL Server 2008

Everyone else in the world can set breakpoints and navigate through their code line by line and see all the important stuff (errors, variable values, output, etc) but anyone writing some TSQL has to usually put in a bunch of SELECT statements,  PRINT statements (or use the business intellegence development studio) to get any clue as to what was going on.

Enter the (return of the) DEBUG button (or ALT+F5 if you’re like me and hate buttons… especially buttons that look like the execute button from Query Analyzer… I mean seriously people… ok I get the fact that yes the execute button execute  has been there since Management Studio 2005 but a green “play” button  play  always means GO GO GO!!! to me… but I digress…)

Weird how a post about loving something turned into a rant… hmmm perhaps I need to lay off the coffee LOL

You need to make sure that you’re a member of the sysadmin server role… but you’re debugging in DEV so I’m sure that wont be a problem… cause no one ever debugs stuff directly in production… I mean that would be silly 😉 hehehe

So let’s work with an example:

DECLARE @RowID INT = 0
WHILE @RowID < 10
BEGIN
   SELECT
@@SERVERNAME
END

 

OK I think it’s pretty obvious what’s wrong with this query… but let’s say for the sake of just showing off the hotness of debugging in SQL Server 2008 you didn’t know why you didn’t get 10 rows returned and ended up with an endless list of your server name LOL

so put your cursor on:

   SELECT @@SERVERNAME
 

and hit F9. You should now see the familar breakpoint red circle. Now if you hit ALT-F5 and look at your "Locals" window at the bottom you should see @RowID with no value. hit ALT-F5 again to continue to debug and your code should stop on:

   SELECT @@SERVERNAME

tsqldebug_ex1

 

 

and now you should see @RowID with a value of 0… great… exactly as expected now let’s go through the next loop… hit ALT-F5 again. We now see in our Results window our server name but in the locals window @RowID is still 0. If we hit ALT-F5 a few more times we’ll see our server name continually being returned but @RowID never changing.

So now if we exit out of “debug” mode. (SHIFT-F5) and add the following line of code after the SELECT @@SERVERNAME:

   SET @RowID = @RowID + 1

Code should now look like this:

 

DECLARE @RowID INT = 0

WHILE @RowID < 10
BEGIN
   SELECT
@@SERVERNAME
  
SET @RowID = @RowID + 1
END

and if we lather, rinse, and repeat the debugging we should now see in our “Locals” window that @RowID increases by 1 every time we loop through and when it gets to 10 we exit out of our loop and we’re done.

So there you go… a nice easy example of debugging in SQL Server 2008… now this only works in SQL Server 2008 currently, if you try and do this in SQL Server 2005 you’ll get a popup window indicating that the Transact-SQL debugger does not support SQL Server 2005 or earlier versions of SQL Server.

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

2 Comments

  1. Jack Corbett says:

    I wonder why they didn’t make it part of SSMS in 2008 and backward compatible with 2005. I know they want you to go latest and greatest, but why punish me for not having the money right now?

  2. Instead of life status reports from dozens of acquaintances, I’m having intimate and fulfilling interactions with the people that I actively seek out. Facebook isn’t the addictive drug it wants us to think it is – you CAN live without it.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.