1

I am trying to create a Stored Procedure that has a parameter which can take an unknown number of values. For this purpose I am using a Table-Valued Parameter that I can query. I was wondering if it is possible to have the Table-Valued Parameter be temporary and have it dropped after the Stored Procedure is executed? I tried reading up about it but from what I have found nowhere is explicitly stated whether the answer is 'Yes' or 'No'.

I'd be very grateful for any help I get. Thanks!

I am using SQL Server 2016.

I declare them as follows: 

CREATE TYPE [schema].[tvp] AS Table ( value INT NULL)  
 GO  

CREATE PROCEDURE [schema].[procedure] ( 
@Param [tvp] READONLY 
) AS BEGIN ..
akrastev
  • 51
  • 3
  • 1
    I think this answers your question , https://stackoverflow.com/questions/5653520/how-do-i-drop-table-variables-in-sql-server-should-i-even-do-this – otri Mar 13 '19 at 17:17
  • What version of SQL Server are you using? If you're using 2016 or newer you could pass your data as JSON through a NVARCHAR parameter. My team has discontinued most use of TVPs in favor of this. – squillman Mar 13 '19 at 17:22
  • How are you declaring the table-valued parameter? Have you defined it as a user-defined table type? – MattPerry Mar 13 '19 at 17:33
  • @MadBert Thanks for the help, I'll look into it. – akrastev Mar 14 '19 at 14:57
  • @squillman I am using 2016. – akrastev Mar 14 '19 at 14:57
  • @MattPerry I have added my code to the summary if that helps. – akrastev Mar 14 '19 at 14:57
  • @squillman Did your team do any performance testing for this? It would be interesting to see a comparison... – Zohar Peled Mar 14 '19 at 15:01
  • It is in fact impossible for a TVP to *not* be temporary, so you're worrying about the wrong thing. – Jeroen Mostert Mar 14 '19 at 15:01
  • @ZoharPeled We did, but we didn't document any of the benchmarks. For what we're doing passing in JSON is a much better alternative. SQL Server's JSON parsing is extremely fast. Disclaimer: the TVP's we used were based off of user defined table types, which was a big part of what we wanted to change. – squillman Mar 14 '19 at 15:13
  • Thanks for posting the code snippet - very helpful to seeing what's going on. The answers below about the contents of the parameter being temporary are spot on. The lifespan of the table-type data passed in exists for the duration of stored procedure execution. Keep in mind a user-defined table type type is different from a temp table or a table variable. Table variables can be declare and used inside your stored procedure - but you need a user-defined table type to pass as a parameter. Every use case is different - but this has worked well for me in the past. – MattPerry Mar 18 '19 at 16:13

2 Answers2

1

Table Valued Parameters are automatically temporary and will be dropped on the SQL Server after the stored procedure executes. On the .net side the parameter will also be dropped in normal "garbage handling".

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
1

Parameters are parameters - they only hold values temporarily. What applies to an int or a varchar parameter while calling a stored proc would apply to TVP as well.

J Sidhu
  • 677
  • 1
  • 4
  • 19