Stack Overflow
Stack Overflow

Questions tagged [table-valued-parameters]

Table-Valued Parameters is a feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set-based operations

Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data.

http://msdn.microsoft.com/en-us/library/bb510489.aspx

Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters. Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set-based operations. Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data. You can create and execute Transact-SQL routines with table-valued parameters, and call them from Transact-SQL code, managed and native clients in any managed language.

411 questions
192
votes
6 answers

How to pass table value parameters to stored procedure from .net code

I have a SQL Server 2005 database. In a few procedures I have table parameters that I pass to a stored proc as an nvarchar (separated by commas) and internally divide into single values. I add it to the SQL command parameters list like…
Marek Kwiendacz
  • 9,524
  • 14
  • 48
  • 72
85
votes
4 answers

Performance of bcp/BULK INSERT vs. Table-Valued Parameters

I'm about to have to rewrite some rather old code using SQL Server's BULK INSERT command because the schema has changed, and it occurred to me that maybe I should think about switching to a stored procedure with a TVP instead, but I'm wondering what…
Aaronaught
  • 120,909
  • 25
  • 266
  • 342
74
votes
6 answers

Entity Framework Stored Procedure Table Value Parameter

I'm trying to call a stored procedure that accepts a table value parameter. I know that this isn't directly supported in Entity Framework yet but from what I understand you can do it using the ExecuteStoreQuery command off of the ObjectContext. I…
Nick Olsen
  • 6,299
  • 11
  • 53
  • 75
68
votes
5 answers

Binding empty list or null value to table valued parameter on a stored procedure (.NET)

I have created a stored procedure that takes a table valued parameter that is a table with a single column of type int. The idea is to simply pass a list of ids into the store procedure and allow the stored procedure to work with the data. …
Brian Jones
  • 701
  • 1
  • 6
  • 6
34
votes
4 answers

How can I insert 10 million records in the shortest time possible?

I have a file (which has 10 million records) like below: line1 line2 line3 line4 ....... ...... 10 million lines So basically I want to insert 10 million records into the database. so I read the file and upload it to SQL…
MD TAHMID HOSSAIN
  • 1,581
  • 7
  • 29
  • 54
25
votes
4 answers

How to ALTER the Table Value Parameter

I am not getting option like 'ALTER TO' when am right clicking on TVP
VInayK
  • 1,501
  • 6
  • 34
  • 47
21
votes
4 answers

Is it possible to use `SqlDbType.Structured` to pass Table-Valued Parameters in NHibernate?

I want to pass a collection of ids to a stored procedure that will be mapped using NHibernate. This technique was introduced in Sql Server 2008 ( more info here => Table-Valued Parameters ). I just don't want to pass multiple ids within an nvarchar…
IamDeveloper
  • 5,156
  • 6
  • 34
  • 50
21
votes
1 answer

Performance of User-Defined Table Types in SQL Server

We have been using User-Defined Table Types to pass a list of integers to our stored procedures. We then use these to join to other tables in our stored proc queries. For example: CREATE PROCEDURE [dbo].[sp_Name] ( @Ids [dbo].[OurTableType]…
KMB
  • 473
  • 1
  • 5
  • 15
19
votes
2 answers

How do I pass a table-valued parameter to Dapper in .NET Core?

I am using .NET Core and Dapper. My problem is that .NET Core doesn't have DataTables, and that's what Dapper uses for table-valued parameters (TVP). I was trying to convert a List to a List, create a SqlParameter with this list…
Nauzet
  • 661
  • 8
  • 20
18
votes
2 answers

Problems with table-value parameter performance

I don't know whether this is an issue with how I'm using them or Microsoft's implementation, but SQL 2008 table-value parameters are painfully slow. Generally if I need to use a TVP it's because I've got lots of records - currently they appear to be…
Keith
  • 150,284
  • 78
  • 298
  • 434
18
votes
1 answer

Setting nvarchar length to maximum in table valued parameters

I want to pass a table valued parameter as a variable to a stored procedure and in the constructor of class SqlMetadata one can specify the length (long maxLength) of the string one wants to add in the column of the…
Arianule
  • 8,811
  • 45
  • 116
  • 174
18
votes
4 answers

Passing parameter of type 'object' in table-valued parameter for sql_variant column

I have a table-valued parameter in SQL Server 2012 defined as: CREATE TYPE [dbo].[TVP] AS TABLE ( [Id] [int] NOT NULL, [FieldName] [nvarchar](100) NOT NULL, [Value] [sql_variant] NOT NULL ) I call it in C# with code that looks roughly…
Dan Hermann
  • 1,107
  • 1
  • 13
  • 27
17
votes
5 answers

Table-Valued Parameters to CLR Procedures in SQL Server 2008 - possible?

This page from SQL Server 2008 BOL, talks about CLR Stored Procedures and has a section labelled, "Table-Valued Parameters", which talks about how they can be advantageous. That's great - I'd love to use TVPs in my CLR procs, but unfortunately this…
philsquared
  • 22,403
  • 12
  • 69
  • 98
14
votes
3 answers

Table valued Parameter Equivalent in Postgresql

In postgresql- what is the equivalent of stored procedure with table valued paramater(MSSQL)?
HamidKhan
  • 545
  • 1
  • 6
  • 9
13
votes
1 answer

EF: Passing a table valued parameter to a user-defined function from C#

I have a user-defined function in SQL Server that accepts a TVP (table valued parameter) as parameter. In EF, how do I call such a function from C# ? I tried using the method ObjectContext.CreateQuery<> but got the following error: The parameter…
Golda
  • 131
  • 1
  • 4
1
2 3
…
27 28