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?
This question is related to
sql
debugging
ssms
table-variable
The answer is
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)
DECLARE @v XML = (SELECT * FROM <tablename> 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.
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/
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"
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
I have come to the conclusion that this is not possible without any plugins.
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.
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.
Why not just select the Table and view the variable that way?
SELECT * FROM @d
Source: Stackoverflow.com