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

Retrieve return value from stored procedure

I have written a table-valued parameter stored procedure, It returns the Id that was inserted. If I run it from a query inside SSMS, it works and returns the value correctly. When I call the procedure from code, it runs, and inserts the data but…
Michael Billingham
  • 117
  • 1
  • 13
0
votes
1 answer

Table Value Parameter missing data table data

I'm new to TVP in SQL Server and trying to understand the basics. I created a sample TVP in the Northwind database in SQL Express. My code from VB.NET is fairly simple (see below). The TVP parameter is empty in the procedure. I've tested the TVP in…
Quadwwchs
  • 1,425
  • 3
  • 15
  • 20
0
votes
2 answers

SQL Merge, Table Value Parameters and GetDate()

I have some synchronization processes which make use of a "LastUpdate" flag to update any records that have changed since the last sync attempt. A little while back I updated the code to utilize table valued parameters, rather than synchronizing…
McGaz
  • 1,354
  • 1
  • 13
  • 22
0
votes
1 answer

Null Reference Exception for Table Value Parameter while executing the stored procedure in C#

I am trying to execute a stored procedure with table valued parameters. These parameters can be null, because of which my code is throwing null reference exception. Is there a way I can pass nullable table valued parameters. Please advise. public…
Rash
  • 300
  • 1
  • 3
  • 19
0
votes
0 answers

Keep track of result sets for each parameter in a table valued parameter

I have got this fairly complex query with multiple recursive members, which I need to run for a list of values, say for example Product Ids (the query outputs parent id along with all its child ids). How can I print out the result set for each of…
PratikGandhi
  • 79
  • 11
0
votes
1 answer

SQL Passing tables as parameters?

I am performing a multiplication that requires two columns from two different tables, and then using the results in a separate query. I think this would be possible as a view: SELECT SUM(A.salesAmt * B.sales%) AS rebateAmount But would a…
Liz
  • 1
  • 1
0
votes
1 answer

SQL Server 2012 Stored Procedure to accept an IN parameter and do multiple inserts

I want to create a SQL Server 2012 Stored Procedure that will take an array from PHP as an IN parameter. The table that the procedure will insert into is called dbo.Users and it will have three columns that I care about: createdby, userid,…
AAA
  • 2,388
  • 9
  • 32
  • 47
0
votes
0 answers

DBNull value for parameter is not supported. Table-valued parameters cannot be DBNull

I am currently trying to pass a two table value parameters from a .net application to a database. The error that I am getting is DBNull value for parameter '@StageNotes' is not supported. Table-valued parameters cannot be DBNull. I have checked…
Simon Price
  • 3,011
  • 3
  • 34
  • 98
0
votes
0 answers

Plain parameters slower than creating table parameters

I recently discovered the strange behavior, that a function (procedures possibly too) runs significantly slower, when I provide simple NCHAR parameters rather than table value parameters. After trying around a bit I also found, that the function…
Romano Zumbé
  • 7,893
  • 4
  • 33
  • 55
0
votes
2 answers

Using a select query as parameter in a table-valued function

I have a function that takes an integer input and returns a table with the related ids from the below table. ID NAME RELATED_ID 1 a null 2 b null 3 c 1 4 d 1 So, select * from fn_getrelatedids(1) returns…
Pindub_Amateur
  • 328
  • 1
  • 6
  • 19
0
votes
1 answer

Using Table Value Parameters in a programmatic execution

I have a s.p. that declares a table value parameter. I've created a TransactionTableType already. DECLARE @TransTVP AS TransactionTableType In order to insert varied data into this TVP, I have to programmatically create and then execute a…
Daniel Raymond Patfield
  • 61
  • 1
  • 8
0
votes
2 answers

Change T-SQL Query Using an INSERT with Table Valued Function into a View Instead

The query concerns one table holding Contacts data. A table valued function is used to return the "status" for each Contact. The insert statement grabs the unique key from the Contacts table and loads it into a variable called @filteredIdList. This…
user3491557
  • 15
  • 2
0
votes
1 answer

Reading a Stored Procedure from Table Value Item

I have a stored procedure called "GetSPTst" which I am calling from a table value function. In this function I am returning a table, the following is my SQL Code. I am getting an error " Msg 443, Level 16, State 14, Procedure TstFunction Line 15.…
Anna Marie Rapa
  • 119
  • 1
  • 3
  • 13
0
votes
0 answers

passesd Table As Sproc parameter insert a partial copy into 2 separated tables

trying update Sql table form a DataTable via a Stored procedure i wanted to avoid multiple round trips on every row insert as i already have a DataTable ready, using Table-valued parameters i can efficiently pass a whole table to SQL Server. problem…
Raj Felix
  • 685
  • 5
  • 16
0
votes
0 answers

Passing multiple Table-Value parameters to stored procedure

I am trying to insert multiple records into multiple tables using one call to a stored procedure which then calls into the corresponding stored procedures that insert into the individual tables. I am using Insight.Database which has the option to…
Kassem
  • 8,116
  • 17
  • 75
  • 116
1 2 3
…
27 28