CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedurestable-valued-parameters
akrastev
akrastev

Reputation: 51

Can Table-Valued Parameters be temporary

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 ..

Upvotes: 1

Views: 993

Answers (2)

J Sidhu
J Sidhu

Reputation: 677

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.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

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".

Upvotes: 1

Related Questions