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 pass a list of integers to a table-valued parameter in C#?

I'm using ASP.NET MVC Framework and SQL Server for this one. I have created a table type which will have the list of ids coming from a variable in C# CREATE TYPE [dbo].[RespondentTypeTable] AS TABLE ( Id INT NOT NULL ) I have created a…
Ankit Das
  • 9
  • 2
0
votes
1 answer

Result not the same while using select statement and table-valued function T-SQL

My ORDER BY clause works differently. If I am using a SELECT statement with ORDER BY everything is fine, all is sorted as i want. But if I use TABLE-VALUED FUNCTION with the exact same SELECT statement inside that function it doesn't sort…
ricarderobl
  • 1
  • 1
0
votes
0 answers

Filter the data inside a stored procedure using Table-Value parameters

I have a stored procedure which receives multiple table-type parameters and I want to filter the data using those parameters. The problem with that is those parameters can contain no rows and in that case a join will not work properly. CREATE TYPE…
Paul Muresan
  • 1
  • 3
0
votes
1 answer

Issue when passing a DataTable to SQL stored procedure containing a table-valued parameter

I can't seem to figure out why I can't get my code below working. I confirmed that my DataTable has records when calling the stored procedure, however the actual table-valued parameter that arrives at my stored procedure has no records. (this was…
kdubbers
  • 33
  • 1
  • 6
0
votes
0 answers

How to pass a table-valued input T-SQL stored procedure parameter using System.Data.OleDb.OleDbCommand

Using System.Data.OleDb.OleDbCommand in C#, how can a table-valued input parameter be passed to a SPROC invocation? Is this possible? If so, I have not discovered an example. However, there are many examples available using…
CalvinDale
  • 9,005
  • 5
  • 29
  • 38
0
votes
2 answers

Sending Null values to User Defined Data table from C#

I have two user defined data tables in stored Procedure for inserting the list of projectid's and AccountId's. From C# I am sending the list of values but only one list will go at a time and another one is null. Example: working on Projectid details…
anil websyn
  • 23
  • 1
  • 7
0
votes
0 answers

I'm trying to create a function that returns a table but I keep getting this error: ORA-00933: SQL command not properly ended

I'm sorry it's my first time working with functions on SQL. I'm using liveSQL.oracle.com to do homework. But I can't seem to work. CREATE TABLE Customer_T (CustomerID NUMBER(4) NOT NULL, CustomerName VARCHAR(25) , …
War Ris
  • 1
0
votes
1 answer

How I can send Multiple TVPs in Nodejs with MSSQL?

I'm trying to send Multiple Tvps to the procedure using npm i mssql exports.createNewApplication = async (req, res) => { try { const ServidorConfig = serverConfig(); const applicationTable = new sql.Table(); …
JokerStyle
  • 1
  • 2
0
votes
1 answer

Stored procedure with table value parameter but ad-hoc?

I have a stored procedure like this: CREATE PROCEDURE [dbo].[GetInventoryData] @supplierId UNIQUEIDENTIFIER, @numbers dbo.ListNumbers READONLY, @locations dbo.ListLocations READONLY AS BEGIN SET NOCOUNT ON; SELECT…
minhhungit
  • 180
  • 4
  • 25
0
votes
0 answers

Using table valued params with Entity Framework

Is there a way to use SQL Server table-valued parameters in Entity Framework in a way that doesn't require you to use a stored procedure or string-based queries like context.Database.ExecuteSqlCommandAsync("") ? Something similar to Dapper where I…
UserControl
  • 14,766
  • 20
  • 100
  • 187
0
votes
1 answer

SQL Stored Procedure - using parameters internally

I have a stored proc that I am passing two parameters into. One parameter is a table-valued parameter and the other is a nvarchar. Here is the stored proc: ALTER PROCEDURE [dbo].[_sp_TestProc] @P1 As [dbo].[FileIdTableType] Readonly, …
Scott
  • 874
  • 3
  • 12
  • 36
0
votes
2 answers

Multiple Inserts in ADO.Net & SP

I need to insert multiple records from ado.net. It should call a SP for updating. I have multiple records as CSV and added them in temporary table in SP. Some Validations needs to be done that. And it get inserted/updated in to main table. Problem…
Mani.M
  • 11
  • 1
0
votes
1 answer

SQL table valued function and Entity Framework using C#

Please, could you give me some tips for my problem with table valued function in SQL? I am designing Windows Forms app in C#, Visual Studio and I am using Entity Framework to join SQL database. I've already created number of table valued functions…
Frantisek
  • 1
  • 1
0
votes
1 answer

Using Table-Valued Parameter's column in stored proc -

I'm passing a TVP with 2 fields (a datetime and a varchar(3)) into a stored proc, and I'm trying to return all the table rows where the table's datetime column is either equal to one of the TVP datetimes or up to a couple of minutes earlier (I don't…
Alex
  • 2,681
  • 3
  • 28
  • 43
0
votes
0 answers

Temp table causing full scan on table unlike Table Variable in a left outer join queries

If I switch out the @GameIds table variable with the #GameIds temp table in the bottom query I get a full table scan on some tables, making a longer execution time, and large memory grant warning. I thought a temp table would give a better…
Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
1 2 3
…
27 28