How to view data in table variables during debugging session in MS SQL Management Studio 2012?
Solution 1
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.
Solution 2
Using the next code you can see the content of your table as XML.
DECLARE @v XML = (SELECT * FROM <tablename> FOR XML AUTO)
It is useful to check what your SELECT statements return. I tested it and it works.
Read more here.
Solution 3
I just simply put in select statements into my script and it displays it to the results window..
select * from @VarTable;
now as I step thru my code and hit the select it will display the values. Then I either comment them out when done testing or set a Testing flag.
Hope this helps
Related videos on Youtube
![Alexander Galkin](https://i.stack.imgur.com/cyHfY.png?s=256&g=1)
Alexander Galkin
From December, 2013 I work as Software Developer at Microsoft Bing. Till August 2017 I was working from the office in Sunnyvale, California and then relocated back to Hamburg, Germany, where I continue to work for the Bing Metrics Team. Until October 2013 I worked as a Senior Developer at Eurofins in Hamburg, Germany. Microsoft Azure Insider Microsoft Expert Student Partner in German MSP Team. 7x MTA | 17x MCTS | 5x MCITP | 5x MCPD | Azure MCSA | MCT Microsoft MCP Transcript (ID: 827426, password: microsoft) Careers 2.0 at SO Blog "Managed meets functional" Twitter Facebook Google+ profile Email: [email protected] @alaudo
Updated on April 28, 2020Comments
-
Alexander Galkin about 4 years
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)
: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.Any idea how I can get the values from my table variables in the debug session?
-
Faiz over 8 yearsPossible duplicate of How to see the values of a table variable at debug time in T-SQL?
-
-
Alexander Galkin almost 11 yearsThank you for your research, mine did not yield any results too. I just didn't want to believe it is not possible, for it renders the debugger completely unusable in my eyes. The problem with "old-skool"-debugging is that we have just "combed" the code to contain no debug/commented out/unreachable statements (as required by our coding policy) and I need to bring them back again ...
-
Naren over 10 yearsThis is not working. I cannot able to see the @v in locals window.
-
RBT over 9 years@Naren I've verified this. It does work in local window and I can see the values populated in the @v variable. I'm using SQL Server management studio for SQL Server 2008 R2.
-
JohnL4 almost 7 yearsYou can add ",ROOT('rootNodeName')" to the "FOR XML" clause. This will collect multiple rows (if any) under a single root, which makes for a legal XML document, which can be viewed with the XML visualizer instead of the text visualizer.