0

I have a s.p. that declares a table value parameter. I've created a TransactionTableType already.

DECLARE @TransTVP AS TransactionTableType

In order to insert varied data into this TVP, I have to programmatically create and then execute a string:

SET @strInsertTrans = N'DECLARE @TransTVP AS TransactionTableType INSERT 
INTO @TransTVP (Value1, Value2, Value3) SELECT Value1, Value2, Value3 
FROM #tmpTable WHERE ...

And then I execute:

EXEC (@strInsertTrans)

The issue is - when I run a select statement:

SELECT * FROM @TransTVP

I get nothing. If I run this code in a separate window (with declared @TransTable variable, and data from the #tmpTable, and run a select - I get data. But somehow, the Table Value Parameter that I declare in my strInsertTrans is not the same as the Table Value Parameter I declare in the s.p. that executes the strInsertTrans.

As an alternative, I tried:

EXEC sp_executesql @strInsertTrans, N'@TransTVP TransactionTableType out', @TransTVP out

But I get the error:

The table variable "@TransTVP" can not be passed to a stored procedure with the OUTPUT option.

This Table Value Parameter is going to be used in a call to ANOTHER s.p. Should I include the s.p. call in the strInsertTrans?? Or better yet, how do I get the resulting data that's stored into this TVP - to be used in my s.p. that's creating it. The final code below:

DECLARE @TransTVP AS TransactionTableType, strInsertTrans nvarchar(MAX)
CREATE #tmpTable (Value1 int, Value2 int, Value3 int)

    SET @strInsertTrans = N'DECLARE @TransTVP AS TransactionTableType INSERT 
INTO @TransTVP (Value1, Value2, Value3) SELECT Value1, Value2, Value3 
FROM #tmpTable WHERE ...

EXEC (@strInsertTrans)

EXEC dbo.UseTableValueParameter @TransTVP

NOTE: One might be tempted to ask me, why do I have to use the strInsertTrans variable; why can't I just do the insert statement direct? The answer is, because there are other variables that require me to use a strInsertTrans and then execute the strInsertTrans, I just don't show them here. Trust me! ;-) I need to execute a string. But I need the data from the results of executing that string. And I can't figure out how to reference it for my last call.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Daniel Raymond Patfield
  • 61
  • 1
  • 8

1 Answers1

1

When you use EXEC(), the passed in code is executed in a new lexical scope. This is why you had to declare @TransTVP in your string, because it wasn't in scope. But all you did was create another table variable unrelated to the outer one. If you want to get data out, the simplest thing to do is use a temporary table instead of a tabled-valued variable. You can always copy the data from the temp table into the table-valued variable after the EXEC() if necessary.

Derek Elkins left SE
  • 2,079
  • 12
  • 14
  • Thanks for your reply. It's the reply I anticipated soon after my post. But I have about seven different procedures using these tvp's. And there are three tvp's altogether. That would mean 3 temp tables each procedure. – Daniel Raymond Patfield Feb 02 '16 at 05:50
  • To elaborate further, I know there were most likely multiple ways to resolve my issue, but given the legacy procedures and processes using them, I have to make do with what I have for now. And what I have is multiple processes that all do 3 things - but in varied manners. And that is - to post back data to 3 tables. Each table has it's own procedure that handles all the nuances of the data it receives for posting back. Each process procedure has to put data into a TVP, one for each of these 3 common procedures. – Daniel Raymond Patfield Feb 02 '16 at 06:08