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

Is there a way to write a function in SQL, which will return a column that you can call in a select statement?

For example, I want to be able to write function 'f()' that I can call as written below. (Note that @tbl is a user-defined memory table that I've previously created with columns Var1 and Var2, and that my function 'f' calls for a parameter of…
swirlingsara
  • 105
  • 1
  • 4
0
votes
1 answer

Cannot call methods on table ? Table Variable

I am trying to put outer apply on the table varible but I am getting error like below Cannot call methods on table. I have Split function which split the string to certain length CREATE FUNCTION Split(@String varchar(MAX), @SplitLength int)…
sandeep_jagtap
  • 1,484
  • 2
  • 17
  • 24
0
votes
0 answers

Oracle - how to iterate on the result of a table valued function?

I have a table valued function: create or replace FUNCTION "GetPositionsWithSymbol" ( par_Symbol "Symbols"."Symbol"%TYPE, ErrorCode OUT…
SJ.Jafari
  • 1,236
  • 8
  • 27
  • 39
0
votes
1 answer

Updating multiple records in one call

I'm using MS SQL server 2008 and I have to update a long list of names in one of the tables. I would like to pass a list of names and have them updated by adding a prefix to them. Say I pass X,Y,Z to the stored procedure and it updates them to…
user2696565
  • 587
  • 1
  • 8
  • 17
0
votes
1 answer

How to Add New Column in Existing Table Value Parameter

I have a TVP like below: CREATE TYPE TestTableType AS TABLE ( Id INT, AnswerId INT ); In this TVP I want to Add one more column AnswerText, How to do that without drop TVP (I did not have permission to drop TVP).
Vijjendra
  • 24,223
  • 12
  • 60
  • 92
0
votes
1 answer

Single SP vs. Multiple

I'm working on a fresh SQL 2008 R2 database. I created a handful of tables and procedures. Currently I have two procedures: [spInsertUser] (@batchID, @firstName, @lastName, ...): creates a single user in the [User] table, associated with a…
that0th3rGuy
  • 1,356
  • 1
  • 15
  • 19
0
votes
1 answer

Obtain information from a row if it meets certain criteria

Okay so the overall scope of the problem is I'm using a table valued function so SSRS can create various reports off the data and then I used another program to stitch all those reports together. So anyway the problem is that I need to obtain a…
Mickey Sly
  • 429
  • 2
  • 6
  • 21
0
votes
1 answer

TSQL Split Function to create multiple rows and columns (table valued function)

I'd like to return an entire table from a string that uses two different text delimiters. Semi-colons for rows, commas for columns. A table valued function could do this but I am far too green to understand how to parse and then append the data to…
Shrout1
  • 2,497
  • 4
  • 42
  • 65
0
votes
1 answer

SQL Server 2008 + TVP merge

I am using Table Value Parameter to merge my records, but on top of that I will also need to delete records from my DB that do not exist in my TVP table. Can anyone please kindly assist. Thanks. An alternative is to simply delete off all records…
k80sg
  • 2,443
  • 11
  • 47
  • 84
0
votes
1 answer

SSDT publish blocked becasue of TVP dependency

I'm trying to publish a change to a user defined table value type but the SSDT publish fails with the following error: This deployment may encounter errors during execution because changes to [dbo].[my_tvp] are blocked by…
Josh Russo
  • 3,080
  • 2
  • 41
  • 62
0
votes
2 answers

How to create int IDs during set insert in SQL Server 2008

I am exploring the use of table valued parameters in stored procedures to do multiple inserts in a single call to the database. The table value parameter contains information that, more or less, reflects the definition of the table I want to insert…
scottmgerstl
  • 765
  • 8
  • 18
0
votes
1 answer

Passing entire rows to SQL CLR function/sproc

I have a process where I need to perform some complex string manipulation on a SQL table containing columns like Forename, Surname, Address1 etc To perform this data manipulation I have setup various SQL CLR C# functions but now I would like to pass…
Abu Dina
  • 207
  • 1
  • 5
  • 12
0
votes
2 answers

Table-valued Function

I've got a Table-valued Function that used to be working, but not anymore or at least takes to much time. The only thing that might have changed is the ammount of data on the tables the query reads. This is the code for the function. ALTER FUNCTION…
ezamora
  • 1
0
votes
0 answers

table-valued function, returning no value?

this is my function: create function ReservationByClient (@reservation_ID int) returns table as return( select c.client_name, c.client_surname, c.client_passport, r.aptID, r.start_date, r.end_date, …
user2099269
  • 11
  • 2
0
votes
2 answers

How to query text column(s) using a Table Valued Parameter?

I have a Table Valued Parameter (@KEYWORD) which just has one column with 0 to many rows of keywords that will query against one or two database (nvarchar) columns (REMARKS and SUPPLEMENTAL_REMARKS). Super simple concept. What I am having trouble…
johntrepreneur
  • 4,514
  • 6
  • 39
  • 52
1 2 3
…
27 28