Is it possible to inspect the contents of a Table Value Parameter via the debugger?

Does anyone know if it is possible to use the Visual Studio / SQL Server Management Studio debugger to inspect the contents of a Table Value Parameter passed to a stored procedure?

To give a trivial exa开发者_如何学运维mple:

CREATE TYPE [dbo].[ControllerId] AS TABLE(
    [id] [nvarchar](max) NOT NULL
)
GO

CREATE PROCEDURE [dbo].[test]
    @controllerData [dbo].[ControllerId] READONLY
AS
BEGIN
    SELECT COUNT(*) FROM @controllerData;
END

DECLARE @SampleData as [dbo].[ControllerId];
INSERT INTO @SampleData ([id]) VALUES ('test'), ('test2');

exec [dbo].[test] @SampleData;  

Using the above with a break point on the exec statement, I am able to step into the stored procedure without any trouble. The debugger shows that the @controllerData local has a value of '(table)' but I have not found any tool that would allow me to actual view the rows that make up that table.

Since you get no joy from the debugger, here is my suggestion. You add an input varaiable to determine if it is in test mode or not. Then if it is in testmode, run the select at the top of the sp to see what the data is.

CREATE TYPE [dbo].[ControllerId] AS TABLE( 
    [id] [nvarchar](max) NOT NULL 
) 
GO 

CREATE PROCEDURE [dbo].[jjtest] 
    (@controllerData [dbo].[ControllerId] READONLY 
    , @test bit = null)
AS 
IF @test = 1
BEGIN
SELECT * FROM  @controllerData
END
BEGIN 
    SELECT COUNT(*) FROM @controllerData; 
END 
GO

DECLARE @SampleData as [dbo].[ControllerId]; 
INSERT INTO @SampleData ([id]) VALUES ('test'), ('test2'); 

EXEC [dbo].[jjtest] @SampleData, 1;  

I got no success trying to do the same what you described. So I guess it isn't possible yet. Will wait for SSMS 2010

It is not possible for table variables, but I built a procedure which will display the content of a temp table from another database connection. (which is not possible with normal queries). Note that it uses DBCC PAGE & the default trace to access the data so only use it for debugging purposes.

You can use it by putting a breakpoint in your code, opening a second connection and calling:

exec sp_select 'tempdb..#mytable'

There is a solution I think you can create another stored procedure and fill the table value parameter and the call your main procedure and then you start debugging from the test procedure you made.