如何在调试时在 T-SQL 中查看表变量的值？

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

提问by Faiz

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?

在调试期间，我们可以在 SQL Server Management Studio (SSMS) 中的表值变量中看到值（行和单元格）吗？如果是，如何？

采纳答案by rortega

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

根据此 Microsoft Connect 链接尚未实现： Microsoft Connect

回答by kirby

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 @vto the watches window.

在要查看表格内容的位置插入上述语句。该表的内容将在 locals 窗口中呈现为 XML，或者您可以添加@v到监视窗口。

回答by James Hulse

This project https://github.com/FilipDeVos/sp_selecthas a stored procedure sp_selectwhich allows for selecting from a temp table.

这个项目https://github.com/FilipDeVos/sp_select有一个存储过程sp_select，允许从临时表中进行选择。

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.

在调试存储过程时，您可以打开一个新选项卡并运行此命令以查看临时表的内容。

回答by messi19

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.

在存储过程中创建一个全局临时表 ##temptable 并在存储过程中编写一个插入查询，将表中的数据插入到这个临时表中。

Once this is done you can check the content of the temporary table by opening a new query window. Just use "select * from ##temptable"

完成此操作后，您可以通过打开一个新的查询窗口来检查临时表的内容。只需使用“select * from ##temptable”

回答by Sousuke

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.

如果您使用的是 SQL Server 2016 或更高版本，您还可以选择它作为 JSON 结果并在 JSON Visualizer 中显示它，它比 XML 更容易阅读，并允许您过滤结果。

DECLARE @v nvarchar(max) = (SELECT * FROM Suppliers FOR JSON AUTO)

回答by solairaja

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/

http://www.simple-talk.com/sql/learn-sql-server/management-studio-improvements-in-sql-server-2008/

回答by Faiz

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

我得出的结论是，如果没有任何插件，这是不可能的。

回答by user3285954

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.

SQL Server Profiler 2014 列出了表值参数的内容。也可能在以前的版本中工作。在存储过程组和 TextData 列中启用 SP:Starting 或 RPC:Completed 事件，当您单击日志中的条目时，您将拥有表变量的插入语句。然后，您可以复制文本并在 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

回答by Kyle Hoener

Why not just select the Table and view the variable that way?

为什么不直接选择表格并以这种方式查看变量？

SELECT * FROM @d

回答by Kenny83

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:

抱歉，我参加聚会有点晚了，但是对于以后偶然发现这个问题的任何人，我发现在存储过程中执行此操作的最简单方法是：

1. Create a new query with any procedure parameters declared and initialised at the top.
2. Paste in the body of your procedure.
3. Add a good old fashioned select query immediately after your table variable is initialised with data.
4. 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.
5. Profit!!

1. 使用在顶部声明和初始化的任何过程参数创建一个新查询。
2. 粘贴到您的程序正文中。
3. 在用数据初始化表变量后立即添加一个很好的老式选择查询。
4. 如果 3. 不是过程中的最后一条语句，请在同一行设置断点，开始调试并直接继续到您的断点。
5. 利润！！

messi19's answer should be the accepted one IMHO, since it is simpler than mine and does the job mostof 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.

messi19 的答案应该是公认的，恕我直言，因为它比我的更简单并且大部分时间都可以完成工作，但是如果您像我一样并且在要检查的循环中有一个表变量，那么这很好地完成了工作无需太多努力或外部 SSMS 插件。