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
0
votes
1 answer

Java parameter passing int[][]

I am trying to write a simple DCT algorithm in java. I want my findDCT method to have as a parameter an integer array like this: public class DCT { private Random generator = new Random(); private static final int N = 8; private int[][]…
Jean-François Beaulieu
  • 4,305
  • 22
  • 74
  • 107
0
votes
0 answers

What is the equivalent of a Table Valued Parameter (TVP) SQL Server insert in ElasticSearch?

In SQL Server, we can define a Table Valued Parameter (TVP) that would contain the same structure as a DataTable in .Net. On the application layer, we can populate a DataTable with thousands of records, then send that table as a single parameter to…
ElHaix
  • 12,846
  • 27
  • 115
  • 203
0
votes
1 answer

Pass multiple rows to UDF in TSQL using PHP PDO

I currently have a query in TSQL that takes in a collection of product IDs and associated prices and calculates discounted pricing. At the moment, the product IDs and initial pricing are in the database, and the query I've written joins against…
walshy002000
  • 99
  • 1
  • 10
0
votes
2 answers

How to pass .NET Collection of objects (Parent-Child) hierarchy to a SQL Server stored procedure

I need to pass a .NET Collection to a stored procedure. My collection contains 1000 records. Each record contains 2-3 child objects of other types. All I need to insert them at once by calling stored procedure from .NET. I already have tried a TVP…
Usman
  • 2,742
  • 4
  • 44
  • 82
0
votes
2 answers

What is the difference between inserting data using Sql insert statements and SqlBulkCopy?

I have a problem of inserting huge amount of data to SQL server. Previously I was using Entity framework, but it was damn slow for just 100K root level records ( containing separately two distinct collections, where each one is further operating on…
Usman
  • 2,742
  • 4
  • 44
  • 82
0
votes
1 answer

How to use Entity framework to pass Table Valued parameter to Stored procedure

I have tables with 2 level hierarchy, Parent->Child->GrandChild I have create stored procedure with three table valued input parameter ParentTable, ChildTable, GrandChild Table. Now, I want to consume it in .net using entity framework. Solution all…
Div
  • 21
  • 5
0
votes
3 answers

Execute function with multiple values in single parameter

I have SQL function which takes parameter as BIGINT like below: FUNCTION [dbo].[fn_doc] ( @DocID bigint ) I want to pass multiple doc ID and want to get results. For which I'm doing like this: declare @DocID bigint SET…
AskMe
  • 2,495
  • 8
  • 49
  • 102
0
votes
1 answer

How to check if parameter is empty in SQL function?

I have in my function this TVP parameter: @SiteTypeId [dbo].[intArray] How can I check if this parameter is empty?
Michael
  • 13,950
  • 57
  • 145
  • 288
0
votes
1 answer

Unable to use Table-Valued Parameter (TVP) in query (node.js & node-mssql)

Good day, I am attempting to export some data from SQL, store it and later compare that data to the result of the same query. The simplest way I can see to do this is using TVP however it doesn't seem to work on node-mssql and it doesn't give me a…
KaoSDlanor
  • 65
  • 7
0
votes
1 answer

How to pass user-defined table type to inline function

I have some complex function that I want to use in number of queries. It gets some list of values and return aggregate value. For example (I simplify it, it is more complex in deed): CREATE FUNCTION Mean(@N Numbers READONLY) RETURNS TABLE AS RETURN…
Artem
  • 1,773
  • 12
  • 30
0
votes
2 answers

Can ObjectDataSource use table-valued parameters

If an ASP.NET web page uses an ObjectDataSource, can you configure it to use a stored procedure that uses table-value parameters? User-defined type: CREATE TYPE [dbo].[integer_list_tbltype] AS TABLE ( [n] [int] NOT NULL, PRIMARY KEY CLUSTERED…
David Gardiner
  • 16,892
  • 20
  • 80
  • 117
0
votes
2 answers

Updating a column value for multiple rows based on a condition

I am trying to update a column for multiple rows. Following is my query UPDATE [Members] SET [Credits]=[Credits]+@FreeCredits WHERE [ID] IN (SELECT T1.[MemberID] FROM @Members T1 RIGHT JOIN [Members] T2 ON…
Aishwarya Shiva
  • 3,460
  • 15
  • 58
  • 107
0
votes
1 answer

Passing SqlDataRecords to procedure is slower almost 9 times than without?

I have a stored procedure with couple parameters. My data table type has 2 columns (int, nvarchar). When I run this stored procedure and pass IEnumerable with defined type then query results on my machine is 9 times slower than the…
dariol
  • 1,959
  • 17
  • 26
0
votes
0 answers

Calling stored procedure with multiple TVP (table-valued parameter) from Entity Framework

I have a stored procedure InsertData with two TVP MessagesTVP & ExceptionTVP. When I called it using EF, at any given point of time, I want to pass just one parameter. It is working fine when I am passing SqlParameter _dataTypeParam = new…
Anil Purswani
  • 1,857
  • 6
  • 35
  • 63
0
votes
1 answer

Multi-statement Scalar to Multi-statement TVF

I have a question on these few lines of code, particularly how this @workTable is being populated with the StartingCost and EndingCost values: DECLARE @workTable TABLE ( ProductId INT , …
Data Engineer
  • 795
  • 16
  • 41
1 2 3
…
27 28