views:

35

answers:

2

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 example:

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.

+1  A: 

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

abatishchev
+1  A: 

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;  
HLGEM
Seems to work well in SQL Server Management Studio - certainly it's better than having no idea at all what is in the table value parameter.Not so great if you're debugging the stored procedure via a C# script though. Your code would have to seperate the debug output from the actual output. It will also fall apart in C# if an exception is raised - as far as I can see there is no way to get any of the results via C# on an exception.
Stephen Edmonds