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
1
vote
1 answer

What would be an instance where MultiStatement Table Valued Function(MTVF) is needed over an Inline Table Valued Function(ITVF) in SQL?

I was able to write two SQL queries, first one is an Inline Table Valued Function(ITVF) and the latter is a Multistatement Table Valued Function(MTVF) for the same task which is quering the worktime of certain employees. -------------- Inline Table…
Ravinath
  • 65
  • 2
  • 14
1
vote
0 answers

SQL inline table function with table value parameter hangs

I wrote a inline table function which works fine on my machine (SQL 08 R2 on Win 7), but in any other environment (SQL 08 R2 on Win 2003 Server) just hangs, even under the lightest loads. There are no differences between database or server…
Mr. TA
  • 5,230
  • 1
  • 28
  • 35
1
vote
2 answers

Is there any way to return IDs of objects inserted with TVP?

I am doing an insert using tvp of large amount of products. After insert I need IDs of these products. Is there any safe way to get them straight away after stored procedure insert?
kali
  • 109
  • 1
  • 2
  • 9
1
vote
2 answers

SQL Native client interface error 8058 when using table-valued parameter, cannot diagnose cause

I've written a function which sends data to a T-SQL stored procedure using table valued parameters. Its the first time I did this and I got stuck by this Error: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is…
antipattern
  • 743
  • 1
  • 9
  • 20
1
vote
2 answers

How to UPDATE/SET inside a function

This is a part of my code inside the table-valued function: DECLARE @Temp1 table (tempid int not null, tasknr varchar(50) null, devcat varchar(50) null, taskop datetime null, taskcl datetime null) (...) DECLARE @Temp1a table (tempid int, tasknr…
Janek
  • 85
  • 2
  • 9
1
vote
1 answer

SQL Server: Describe a Table-Valued Parameter

What is the equivalent to EXEC sp_columns ; but for SQL Server Table-Valued parameters? Basically, I'm looking for T-SQL or an in-built stored procedure that returns column information (name, data type) for user-created Table-Valued…
Dan Forbes
  • 2,734
  • 3
  • 30
  • 60
1
vote
1 answer

Table valued parameter with primary keys changes

I am just starting to use table valued parameter in sql server 2008 to update table. basically, I create user defined table type mapping to an existing table column by column, and use that as table valued parameter, passing data to the server for…
Qstonr
  • 815
  • 1
  • 9
  • 11
1
vote
1 answer

Creating a table type param in SQL Server Stored Procedure

Is it possible to declare/create the table type param(TVP) in Stored Procedure itself instead of creating the table value type separately in the schema and then using it in Stored procedure. i.e., create procedure proc1( @table1 table(id int)…
user3733328
  • 133
  • 1
  • 7
1
vote
0 answers

SQL Server Express - TVP Parameterized TVF with recursive CTE freezing at execution

Just came across a very strange server behavior - namely: I got TVF with one TVP argument as below CREATE FUNCTION [BUD].[Foo] (@IdAgrs IdTable READONLY) RETURNS TABLE AS RETURN ( WITH recursive_CTE AS ( --> Select here is…
L5k
  • 23
  • 4
1
vote
2 answers

DbContext ExecuteSqlCommand - Table Valued Stored Procedure

I am having an issue calling a stored procedure off of my DbContext. The stored procedure takes in a table valued parameter. The syntax of the procedure is: ALTER PROCEDURE dbo.SaveResults @resultID int, @positiveResults AS…
Patrick
  • 5,526
  • 14
  • 64
  • 101
1
vote
0 answers

Need an alernative to While Loop for simple query using SQL stored procedure or function

I have a simple parent / child record set that I currently loop through and display with code on the client that I want to move to a procedure for performance reasons. This used in a classic .asp web page to create a dynamic html table. The current…
mham99
  • 21
  • 8
1
vote
2 answers

Dapper.Tvp Assembly is does not have a strong Name

Recently I was working with Dapper.Net and it is awesome so far for dynamic object manipulations with sql, Great work by Dapper team. But there is a need to have bulk insert and update and i used table valued parameter which is better in terms of…
Husni Jabir
  • 545
  • 1
  • 6
  • 17
1
vote
2 answers

sql server stored procedure IN parameter

In SQL Server, I am passing String as parameter : @Param1 = Test,Test1,Test2 I am formatting it to: @Param1 = 'Test','Test1','Test2' When I am trying to use this in SELECT statement with IN parameter, it is not returning any data SELECT * FROM…
user3617176
  • 13
  • 2
1
vote
0 answers

DataReader for Table Valued Parameter in VB.Net does not work

I'm trying to use DataReader object retrieving data from a text file and use it as a source for table valued parameter of a stored procedure (SQL Server 2012). The procedure works fine when I try it within SQL Server (SSMS), text file data is…
user3368333
  • 11
  • 2
1
vote
1 answer

Specify name of table types in SQL Server

Create a new database in MS SQL Server 2008 R2 and then create a new table type with the following command CREATE TYPE typeMyType AS TABLE (ID INT) Now execute the following query SELECT OBJECT_NAME (object_id) AS ObjectName, * FROM sys.indexes…
Marc
  • 9,012
  • 13
  • 57
  • 72
1 2 3
…
27 28