How to see the values of a table variable at debug time in T-SQL

Question

Can we see the values  rows and cells  in a table valued variable in SQL Server Management Studio  SSMS  during debug time  If yes  how

User · Answer

DECLARE  v XML    SELECT   FROM  lt tablename gt  FOR XML AUTO    Insert the above statement at the point where you want to view the table s contents   The table s contents will be rendered as XML in the locals window  or you can add  v to the watches window

User · Answer

That s not yet implemented according this Microsoft Connect link  Microsoft Connect

User · Answer

In the Stored Procedure create a global temporary table   temptable and write an insert query within your stored procedure which inserts the data in your table into this temporary table   Once this is done you can check the content of the temporary table by opening a new query window   Just use  select   from   temptable

User · Answer

I have come to the conclusion that this is not possible without any plugins

User · Answer

This project https   github com FilipDeVos sp select has a stored procedure sp select which allows for selecting from a temp table   Usage   exec sp select  tempDb   myTempTable    While debugging a stored procedure you can open a new tab and run this command to see the contents of the temp table

User · Answer

Just use the select query to display the table varialble  where ever you want to check   http   www simple-talk com sql learn-sql-server management-studio-improvements-in-sql-server-2008

User · Answer

Sorry guys  I m a little late to the party but for anyone that stumbles across this question at a later date  I ve found the easiest way to do this in a stored procedure is to    Create a new query with any procedure parameters declared and initialised at the top  Paste in the body of your procedure  Add a good old fashioned select query immediately after your table variable is initialised with data  If 3  is not the last statement in the procedure  set a breakpoint on the same line  start debugging and continue straight to your breakpoint  Profit     messi19 s answer should be the accepted one IMHO  since it is simpler than mine and does the job most of the time  but if you re like me and have a table variable inside a loop that you want to inspect  this does the job nicely without too much effort or external SSMS plugins

User · Answer

SQL Server Profiler 2014 lists the content of table value parameter  Might work in previous versions too  Enable SP Starting or RPC Completed event in Stored Procedures group and TextData column and when you click on entry in log you ll have the insert statements for table variable  You can then copy the text and run in Management Studio   Sample output   declare  p1 dbo TableType insert into  p1 values N A  N B   insert into  p1 values N C  N D    exec uspWhatever  PARAM  p1

User · Answer

Why not just select the Table and view the variable that way    SELECT   FROM  d

User · Answer

If you are using SQL Server 2016 or newer  you can also select it as JSON result and display it in JSON Visualizer  it s much easier to read it than in XML and allows you to filter results   DECLARE  v nvarchar max     SELECT   FROM Suppliers FOR JSON AUTO

[sql] How to see the values of a table variable at debug time in T-SQL?

Examples related to sql

Examples related to debugging

Examples related to ssms

Examples related to table-variable