The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
-1
votes
1answer
25 views

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

CREATE PROCEDURE [dbo].[GetFruitName] ( @quantity int ) AS BEGIN SET NOCOUNT ON; INSERT INTO Table1(fname,fprice,quantity) SELECT f.name,f.price from ...
1
vote
2answers
109 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 ...
5
votes
1answer
213 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 ...
1
vote
1answer
151 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, ...
0
votes
3answers
78 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 ...
2
votes
1answer
956 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 ...
-1
votes
1answer
36 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 ...
1
vote
2answers
890 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 ...
1
vote
0answers
243 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] [...
3
votes
1answer
3k 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 ...
4
votes
1answer
241 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),...
6
votes
1answer
832 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 ...
4
votes
1answer
945 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-...
4
votes
1answer
155 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 ...
0
votes
1answer
896 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 ...
1
vote
0answers
56 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 ...
3
votes
2answers
279 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 ...
2
votes
2answers
4k 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?
3
votes
1answer
2k 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 ...
1
vote
1answer
611 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 ...
2
votes
0answers
392 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 ...
-2
votes
1answer
2k 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 ( ( ...
5
votes
1answer
7k 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 ...
19
votes
2answers
8k 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 ...
2
votes
1answer
297 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 ...
8
votes
2answers
23k 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 ...