Table-Valued Parameters not visible

Question

For a reason unknown to me, I cannot access the contents of my TVP. The debugger says @_AccountList and @AccountTVP are tables, but I cannot view the contents, print them, nor will intellisense offer me auto completion for a column. I feel like probably declared something wrong or have an ambiguous definition. Any suggestions?

CREATE TYPE AccountList
AS TABLE
(
    AccountNumber varchar(50),
    AccountType varchar(50)
) 
GO

CREATE PROCEDURE [dbo].[updateNumbers_ArchiveDB]
 @_AccountList AccountList READONLY,
 @padding int,
 @proc_dateStart datetime,
 @proc_dateEnd datetime
 AS 

 DECLARE @AccountTVP AS AccountList;

 BEGIN
 SET NOCOUNT ON;

SELECT * FROM @AccountTVP;


 UPDATE dbo.Archive2007001 
    SET LogicalAccount = @padding + AccountNumber
    FROM dbo.Archive2007001 INNER JOIN @AccountTVP AS tvp
    ON dbo.Archive2007001.LogicalAccount = tvp.AccountNumber
    WHERE ProcessDate BETWEEN @proc_dateStart AND @proc_dateEnd

 UPDATE dbo.DailyArchive 
    SET LogicalAccount = @padding + AccountNumber
    FROM dbo.DailyArchive INNER JOIN @AccountTVP AS tvp
    ON dbo.DailyArchive.LogicalAccount = tvp.AccountNumber
    WHERE ProcessDate BETWEEN @proc_dateStart AND @proc_dateEnd


 -- does not work PRINT N'tvp.AccountNumber is ' + @AccountTVP.AccountNumber

 END

Here is how I am executing the procedure.

declare @p1 dbo.AccountList
insert into @p1 values(N'Account Number',N'Account Type')
insert into @p1 values(N'7463689',N'Basic')
insert into @p1 values(N'1317893',N'Premium')
insert into @p1 values(N'2806127',N'Basic')

exec updateNumbers_ArchiveDB 
@_AccountList=@p1,
@padding=N'111',
@proc_dateStart='2008-01-04 11:24:46',
@proc_dateEnd='2008-01-04 11:24:46'

Answer: The data I was looking for was loaded in @_AccountList, not @AccountTVP.

But @AccountTVP is empty. @_AccountList is what is passed to the procedure. — paparazzo, Aug 12 '14 at 20:05
Ya, I figured that out. Strange, I could have sworn I tried accessing @_AccountList as well. Thanks anyway. — user1729696, Aug 12 '14 at 21:13

score 2 · Answer 1 · answered Aug 12 '14 at 19:04

2

@AccountTVP.AccountNumber represents a row of data, not a single value, so trying to print it will not work.

You should be able to see the incoming values by using a SELECT statement:

SELECT * FROM @_AccountList;

It seems like your SELECT statement from the top of the stored procedure should let you see the values, however, you have not actually set any values in @AccountTVP so it would be empty.

I am not sure but I suspect the debugger may not work because AccountList is a custom type.

answered Aug 12 '14 at 19:04

Aaron D

5,817
1
36
51

Edited the OP with how I am executing the procedure. I feel the select statement should work as well. Is there a way I can print just a single value from @AccountTVP? – user1729696 Aug 12 '14 at 19:12
```@AccountTVP``` is going to be empty, you never insert any data into it. But if you wanted to get a single value, you could ```SELECT TOP 1 AccountNumber FROM @AccountTVP``` – Aaron D Aug 12 '14 at 20:44

Table-Valued Parameters not visible

1 Answers1