Questions tagged [table-valued-parameters]

TVPs are a feature of Microsoft SQL Server. Introduced in SQL Server 2008, TVPs allow for sending a structured table of data to a stored procedure or function. Using TVPs can improve performance over sending multiple rows as XML or doing row-by-row processing.

Filter by
Sorted by
Tagged with
14 votes
1 answer
2k views

Why does a simple natively compiled stored procedure run out of memory when table variables are used?

My version of SQL Server is SQL Server 2019 (RTM-CU18). The following repro code requires that an in memory filegroup is created. For anyone following along, please remember that an in-memory ...
Joe Obbish's user avatar
  • 32.2k
2 votes
1 answer
213 views

Multiple Computed, Dependent Columns on INSERT in SQL Server?

I'm relatively new to database programming and am looking for some some theory/best practices and feedback on an issue involving multiple dependent, cascading, computed columns for an OLTP SQL Server/...
Will Adams's user avatar
9 votes
2 answers
251 views

Why does this TVF throw error 9820 with GETDATE() as an input parameter?

I am testing on SQL Server 2019 CU14. Consider the following table-valued function created against a SQL Server database with compatibility level 130 or 140: -- requires database compat 130 or 140 to ...
Joe Obbish's user avatar
  • 32.2k
3 votes
1 answer
865 views

Procedures using TVPs are slower when the TVP numeric value gets larger?

A legacy application has a nightly job that repeatedly calls some store procedure using a TVP and passes in batches of 10,000 ids that are in sequential order that it needs to process. Now that the ...
Michael B's user avatar
  • 519
-1 votes
1 answer
45 views

insert a variable and table value as parameters to procedure [closed]

CREATE PROCEDURE [dbo].[GetFruitName] ( @quantity int ) AS BEGIN SET NOCOUNT ON; INSERT INTO Table1(fname,fprice,quantity) SELECT f.name,f.price from ...
shivraj Jacky's user avatar
1 vote
1 answer
2k views

How to pass a table-valued parameter to sp_execute_external_script?

I have a stored procedure that calls an external script via a SQL Server language extension. I would like for my stored procedure to use a caller supplied table valued parameter (TVP) to then submit ...
user4321's user avatar
  • 215
6 votes
1 answer
327 views

Generic TVP tradeoffs?

Is there a best practice or strategy for table types used in TVPs? For instance, given the following: CREATE TABLE dbo.Colors ( Id int identity PRIMARY KEY, Name nvarchar(100), ); CREATE ...
Daniel Liuzzi's user avatar
1 vote
1 answer
3k views

alternatives to passing temp table as a parameter to a stored procedure

some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure: USE MASTER GO ALTER PROCEDURE dbo.sp_getDBSpace @Radhe sysname=null, ...
Marcello Miorelli's user avatar
0 votes
3 answers
132 views

Slow performance matching columns to lists of values

I've made a stored procedure to get all results which match specific students and objectives. CREATE PROCEDURE [dbo].[GetResults] @students [IdList] READONLY, @objectives [IdList] READONLY AS SELECT ...
James's user avatar
  • 103
4 votes
1 answer
5k views

Efficiently bulk upsert unrelated rows

As part of a manual replication process between databases with different but related schemas, for each table, we identify new and modified records in the source database and feed them via a table ...
Edward Brey's user avatar
-1 votes
1 answer
76 views

How to acquire a table result set from "Table - valued function" when the result set has variable structures?

I need to write a Table - valued function from which I can acquire a table result set with different structure each time. another thing is that I need to declare variables inside my function . I've ...
user avatar
1 vote
2 answers
7k views

SQL Server: Pass table name into table valued function as a parameter

All of my tables have those two columns: [Id] [uniqueidentifier] NOT NULL [Revision] [bigint] IDENTITY(1, 1) NOT NULL Every other time I write a query, I want to eliminate some of the rows. The ...
vorou's user avatar
  • 193
0 votes
0 answers
1k views

MySQL Table Value Parameter?

I come from Microsoft SQL Server - but am converting a database to MySQL. I have this syntax which works excellent on my MSSQL install CREATE TYPE [dbo].[TableValue_ExceptionsTable] AS TABLE( [TN] [...
HotTomales's user avatar
3 votes
1 answer
8k views

Passing an array of integers as a parameter of an (inline) user defined function (SQL Server)

I have TBV function . Like this ALTER FUNCTION [dbo].[fn_Functiont] ( @accessibleIds ListAccesableIds READONLY ) RETURNS TABLE AS RETURN ( SELECT d.*, b.Name AS Name, ps.Name AS ...
So_oP's user avatar
  • 145
4 votes
1 answer
808 views

Proper use of varbinary type in MS SQL Server

I am at a new position and I am being told to implement a stored procedure that will accept a list of user ids and update a flag. When I suggested the use of a table value parameter (array emulation),...
bbqchickenrobot's user avatar
6 votes
1 answer
3k views

Is there an upper limit to the SqlDbType.Structured ADO.NET type?

Is there an upper limit to the number of rows\total size of the SqlDbType.Structured type when sending data to a Table parameter in a sproc? I can't find anything to suggest there is so I'm assuming ...
BanksySan's user avatar
  • 991
4 votes
1 answer
1k views

View Existing Table-valued Parameters

I have seen many tutorials on how to create a table-valued parameter - but how do you view a table-valued parameter after it has been created? I would prefer a way of using the GUI in SSMS but T-...
BellHopByDayAmetuerCoderByNigh's user avatar
4 votes
1 answer
165 views

Can you create a table param WITHOUT a pre-defined TVP in a SP?

In the body of an SP I have the following line of code: DECLARE @assetCode NVARCHAR(50) = ( SELECT DISTINCT [Asset Code] FROM <some table> WHERE hProp = xxx ) Is it possible to do the same ...
Zach Smith's user avatar
  • 2,360
0 votes
1 answer
2k views

Delete rows in a table based on a table value parameter

I have a table A and a user defined table type on which a table value parameter (tvp) tvpA is based. Both have the same column structure except that tvpA is not defined with any constraint. What I'd ...
steven's user avatar
  • 1
1 vote
0 answers
69 views

Are there any pitfalls to be aware of when using dynamic type-coercion in SQL Server?

I want to insert into a database with a query that looks something like the following: INSERT INTO <table name> (<set of column names>) VALUES (<set of corresponding values>); As I ...
Zach Smith's user avatar
  • 2,360
3 votes
2 answers
500 views

Table Value Constructor as Procedure Parameter

I can find information on creating procedures with table-valued parameters, and information on table literals, but I can’t find any mention of the two combined. Suppose I have a procedure defined ...
Manngo's user avatar
  • 2,819
2 votes
2 answers
11k views

Get name and definition of all table types

I know how to create a table type (to be used as a table-valued parameter) - but once created, how can I view it?
RedLight GreenLight's user avatar
3 votes
1 answer
3k views

Why are Column Defaults on my User-Defined Table Type (UDTT) not being honored?

I create a table type in SQL: CREATE TYPE [dbo].[MyObject_TableType] AS TABLE ( [Name] VARCHAR(MAX) DEFAULT '', [Index] VARCHAR(MAX) DEFAULT '' ) I build a DataTable and populate it with one ...
Rainbolt's user avatar
  • 800
1 vote
1 answer
927 views

Forcing correct execution plan when passing a table UDT to a table function

I have a scalar function that returns a big XML, created over a bunch of invoices. The exact list of invoices to feed to the function can be calculated using several different ways, but the function ...
GSerg's user avatar
  • 1,342
2 votes
0 answers
488 views

Cross database - Table valued Stored Procedure

I have the same table type in two databases (DB1 and DB2), DB1 has an stored proc (sp1) which has the above type as an input parameter. Now, I want invoke this SP from DB2. But, I am getting this ...
Karthik Sreenivasan's user avatar
-2 votes
1 answer
3k views

why unable to execute table-valued function?

Here is table-value function I have declared like: CREATE FUNCTION fn_GetMedian(@List TypeMedian READONLY) RETURNS INT AS BEGIN RETURN ( Select ( ( ...
Kaishu's user avatar
  • 517
5 votes
1 answer
9k views

Getting error "must declare the scalar variable" in SQL function even though it is declared

I am altering function like: ALTER FUNCTION [dbo].[fn_CalculateListing](@Listing TypeListingDatePrice READONLY) RETURNS TypePreviousListingResult AS BEGIN DECLARE @tbl_ListingDateDetails ...
Kaishu's user avatar
  • 517
21 votes
2 answers
14k views

Why must TVPs be READONLY, and why can't parameters of other types be READONLY

According to this blog parameters to a function or a stored procedure are essentially pass-by-value if they aren't OUTPUT parameters, and essentially treated as a safer version of pass-by-reference if ...
Erik's user avatar
  • 4,771
2 votes
1 answer
339 views

Understanding execution plans using TVPs

I have some slow running queries (8 seconds across 1M records grouped by months) and have been trying to make sense of the execution plans. We use around 10 TVPs to send in a set of filters that the ...
SteveB's user avatar
  • 131
17 votes
3 answers
39k views

How do I check for a null or empty table-valued parameter?

I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be ...
Dan's user avatar
  • 535