How to view data in table variables during debugging session in MS SQL Management Studio 2012?

Alexander Galkin picture Alexander Galkin · Jul 12, 2013 · Viewed 21.7k times · Source

I would like to debug a complex T-SQL script using SSMS 2012.

I can run the script in debug mode and place breakpoints, as well as step through my script, but I can't see the values stored in my table variables.

In the Locals window I see all these variables, but their value is shown as (table):

Locals window

There is no way to view the content of the variable through the context menu or by clicking on the variable.

I tried to use the Immediate Window to run a query on the table variable, but this seems not to work either.

Immediate Window

Any idea how I can get the values from my table variables in the debug session?

Answer

Damien_The_Unbeliever picture Damien_The_Unbeliever · Jul 12, 2013

Whilst I can't find any documetation, anywhere, that explicitly states that you cannot inspect table variables, I don't believe that it's possible. From Transact-SQL Debugger

Locals and Watch. These windows display currently allocated Transact-SQL expressions. Expressions are Transact-SQL clauses that evaluate to a single, scalar expression. The Transact-SQL debugger supports viewing expressions that reference Transact-SQL variables, parameters, or the built-in functions that have names that start with @@. These windows also display the data values that are currently assigned to the expressions.

(My emphasis)

That is, you can only inspect scalars.

As to your attempt to use the Immediate window, the Limitations on Debugger Command and Features says:

The Immediate window is displayed, but you cannot do anything useful with it, such as setting a variable to a value, or querying the database.


I've never really used the debugger much - everytime I've looked into it, I encounter limitations like this.

That's why I still tend to use "old-skool"/"printf" approaches to debug SQL - include extra SELECT *s liberally throughout the code showing the current state of tables, and extra PRINT or RAISERROR messages that show other states, etc. And then just run the code normally, until you've bashed it into shape.