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
0 answers

How to send around 5+ million id's to a SQL Server stored procedure to get sum of there corresponding sizes? SQL 2012

I have used table valued parameter to do so. But I am not aware of the restrictions (if any) of SQL Server on the size of DataTable that we are passing. Additionally I need to know if there are hidden cons of doing this by TVP method and any other…
Sangram Patil
  • 98
  • 1
  • 8
0
votes
1 answer

MS SQL Server 2014 - Deadlock error when creating a user-defined table type and use it in the same transaction

For testing purposes, I am creating a user-defined table type and then use it to pass some testing data in a table-valued parameter to a query. However, if I do so in the same transaction, I always get a deadlock error. Unfortunately, I need to do…
geeko
  • 2,649
  • 4
  • 32
  • 59
0
votes
0 answers

Executing a Merge From a Local Table Valued Parameter or Temp Table Source to a Linked Server

My goal is to load data from a webapp into a database on a linked server. Users will create several thousand rows of data with 8 columns. I was trying to implement a version of the Passing a Table Valued Parameter to a Parameterized SQL Statement in…
Basic.Bear
  • 111
  • 1
  • 9
0
votes
2 answers

How to Pass string array to Stored procedure and perform insert query

Have seen lot of example for passing array values to stored procedure using table valued parameter and i followed the same but the values are not get inserted inside the table. Here i have to sent a string array having some ids and a int value. i…
codeseeker
  • 196
  • 1
  • 13
0
votes
2 answers

Iterating through a TVP before inserting records?

I'd like some help writing the following sproc: I have SQL Server 2008 sproc that accepts two integer values (@ID1 and @ID2) and a data table/TVP. The TVP table contains several fields, ie. Title and Description. I want to iterate through the TVP…
ElHaix
  • 12,846
  • 27
  • 115
  • 203
0
votes
1 answer

How to pass XML data as a parameter to a TVP param from C# to Sql server

I want to pass a paramter of type XML to a param in a TVP. I tried the below code, which does not work. I am unable to figure out if there is a way to pass an xml data to a TVP in sql server. sql CREATE TABLE XmlSample(Id INT, Profile XML); CREATE…
Rahul
  • 51
  • 1
  • 4
0
votes
2 answers

OPENQUERY in table-valued function with parameters

I am new in using OPENQUERY and I have encountered a problem when I use it inside a table-valued function, below is my query INSERT INTO @returnList SELECT * FROM OPENQUERY(lnk_db, 'SELECT s.posting_date -COALESCE(max(a.due_date::date),…
pryxen
  • 381
  • 2
  • 22
0
votes
1 answer

Calling function with table valued parameters in Postgres

I have been facing a problem from since morning and have spent many hours but failed to call below given function. Function definition: CREATE OR REPLACE FUNCTION public.proc_mc2cdnpf_insertupdatev3( tblnotesv3 typupdate_notesv3, …
Muhammad Waheed
  • 1,048
  • 1
  • 13
  • 30
0
votes
2 answers

SQL Table Value Parameter only works when its data is copied to temporary table

I'm passing the data through c# to sql stored procedure using dapper. c# (using dapper): var dt = new DataTable("dbo.TVPtype"); dt.SetTypeName("dbo.TVPtype"); dt.Columns.Add("ID", typeof(int)); dt.Rows.Add(5); _db.Execute("mysp", param: new {…
Muhamad Jafarnejad
  • 2,521
  • 4
  • 21
  • 34
0
votes
2 answers

Table-Valued Parameter Recieving Invalid Data Type Error

I have an array of data from a console app I've developed that I need to transfer into a SQL database. As SQL injections are volatile, I decided to try passing it through a stored procedure via table-valued parameter. I created the table type with…
Dan
  • 97
  • 1
  • 12
0
votes
0 answers

Call existing stored procedure from another stored procedure with table valued parameters

Goal: Call the existing stored procedure many times with different sets of values, e.g. Parameter 1 {100000, 100001, 100002....}, .... dbo.ExistingStoredProc = existing stored procedure that takes 2 parameters {int, varchar(20)} I use table valued…
icernos
  • 395
  • 3
  • 6
0
votes
0 answers

User defined type from stored procedure not detected in LINQ

I have created this stored procedure: CREATE PROCEDURE [dbo].[zsp_MoveItemsToFolder] (@IdListToMove IdListToMove READONLY, @FolderId INT, @UserId INT) AS BEGIN DECLARE @Rowcount INT = 1; WHILE (@Rowcount > 0) UPDATE…
User987
  • 3,663
  • 15
  • 54
  • 115
0
votes
1 answer

Should inserting rows with TVP be in transaction?

I have a TVP type List_Of_Items, and want to insert multiple rows at once, so my choice is to use stored procedure with TVP parameter like this below. Is such a inserting safe and I can get rid of the transaction? Or its potenially dangerous as the…
CSharpBeginner
  • 1,625
  • 5
  • 22
  • 36
0
votes
1 answer

Table valued parameter problem involving operation

I am creating a view which uses a table valued function. Here is a simple function: CREATE FUNCTION TEST(@COD INT) RETURNS TABLE AS RETURN SELECT @COD COD When I execute: SELECT * FROM DBO.TEST(1) it runs perfect, but when I make an operation…
Enzo Quiroz
  • 3
  • 2
0
votes
0 answers

How to insert a TVP into a table with an Identity column using node mssql?

My typescript azure function, interacts with my SQL Server database. I can use TVPs to insert data into the database, except for when the receiving table type contains an IDENTITY column. My table has 3 columns, the first being an IDENTITY column.…
tuathail
  • 48
  • 5
1 2 3
…
27 28