413 questions
0
votes
1
answer
347
views
Pass TVP data to a SQL query in Python (pyodbc)
I am trying to pass a table valued parameter from Python to SQL Server query.
This is my table value that I want pass as a param.
table_values = [
'table_type_name',
'dbo',
(1,...
- 1
-1
votes
1
answer
89
views
Use Table Valued Parameter in Merge sql statement
I am trying to use sql server's MERGE command. However, I am not sure, how do we pass it as a parameter using Java.
I am setting up SqlParamSource as
private SqlParameterSource ...
- 19
-1
votes
2
answers
78
views
Stored Proc with DataTable Parameter - How to perform conditional update without looping through the datatable
I have a stored procedure that takes a DataTable (of int Ids) as a parameter. I perform an update using the datatable. After doing this I would like to add a further conditional update, which may ...
- 65
0
votes
0
answers
48
views
SQL Server Table-Valued Parameter and Python [duplicate]
I am writing a simple ETL. The source is a spreadsheet. The destination is a SQL Server table. The data are integers and strings.
I would like to pass all of the data at once to SQL Server through a ...
- 1,791
0
votes
0
answers
104
views
PHP PDO Throws Error while passing an array to SQL Server Table Valued Parameter
I want to pass an array to a Table Valued Parameter in a MSSQL Stored Procedure using PHP PDO Drivers.
I am getting an error:
Uncaught PDOException: SQLSTATE[IMSSP]: An invalid type for parameter
8 ...
1
vote
1
answer
540
views
Python call procedure with TVP parameter that returns multiple tables in SQL server
I am trying to call a stored procedure with sqlalchemy to a microsoft sql server. The procedure takes a custom table valued parameter and returns back multiple tables.
The only way to send a TVP i ...
- 19
-2
votes
1
answer
291
views
Delete records using TVP in stored procedure [duplicate]
I have a table called Student
sno Name Course Fee Section
1 AAA BCA 10000 A
2 BBB BCom 9000 B
3 CCC BTech 12000 B
I created a temp table ...
- 435
0
votes
0
answers
329
views
Insert List of dictionary data into SQL Server using pytds tvp
I would like to implement list of dict data to be loaded into SQL Server DB using pytds tablevalueparams tvp in my python code. \
I tried below mentioned code and it's not working as expected.
def ...
- 31
-1
votes
1
answer
475
views
How to insert values into SQL Server table using Stored procedure in Python
I am new to pytds. Facing TypeError: not enough arguments for format string issue while inserting data into SQL Server.
Issue I am getting is:
Traceback (most recent call last):
File "c:/Users/...
- 31
3
votes
1
answer
754
views
How do I convert a string of comma separated integers to just comma separated ints for a ExecuteSql command?
I'm getting an exception because the ids are a string
ex. '(1,2)'
I need it be comma separated but not a string, otherwise I get an exception saying:
Exception Message: Conversion failed when ...
- 14.8k
1
vote
2
answers
1k
views
Table-Valued User-Defined Function parameter used for IN clause
How can I create a Table-Valued User-Defined Function in SQL Server that takes an input parameter for an IN clause?
I'm trying to write this very simple function:
CREATE FUNCTION dbo....
- 191
0
votes
0
answers
182
views
Oracle - How to send query result as parameter to a function
I need to create a function that get 2 string and a table parameters to store them i a table like that
create table Result(
Id NUMBER GENERATED BY DEFAULT AS IDENTITY,
RefCode VARCHAR2 (10),
...
- 1,418
0
votes
0
answers
181
views
Problem with Table Valued Parameter SQL server and Python Pyodbc
I've created a test table, then a Type Table and then a SP:
CREATE TABLE prueba (
id int IDENTITY,
datos varchar (20),
nums NUMERIC (10,2)
CONSTRAINT PK_PRUEBA PRIMARY KEY (id)
)
...
0
votes
1
answer
98
views
Is a table-valued parameter (UDT) data entry specific to a user session?
I use the user-defined table parameter to track the keyed in values and pass it to a stored procedure. Syntax works fine. No problem with it.
Question:
Does it creates multiple instances at the same ...
- 31
0
votes
1
answer
885
views
Insert values into table from TVP and variable [duplicate]
I have an input TVP
@_inputTvp TableType Readonly
and local variable
declare @_localVar varchar
I know how to insert TVP to a table, and variable value to a table. But now I have 2 source of data (...
- 3
0
votes
0
answers
275
views
Calling table value MSSQL stored procedure from python
I want to call a MSSQL Stored Procedure from Python which takes table value as a parameter, and which was earlier triggered from API by passing
{
"data":
[{"xyz": 340, ...
0
votes
1
answer
286
views
Can a stored proc be declared with a table-valued parameter along with other parameters in SQL Server? [closed]
Edit: one of the comments mentions "misuse" of parentheses around the parameter. I'm attaching a screen-capture of the Microsoft documentation page showing those parentheses; and my stored ...
- 8,990
0
votes
1
answer
716
views
Stored procedure with CASE and WHERE coming from a table value parameter
In a SQL Server stored procedure, can I use two separate columns from a table-valued parameter (TVP) in both the CASE and WHERE clauses?
In my TVP, I have two columns like so:
CREATE TYPE tt_Index AS ...
- 31
0
votes
1
answer
93
views
SQL Update in Batches using Table Type Parameters
I am trying to update a table using table type parameter. Currently I am using the below query. But I would like to perform update in batches having batch size as one of the parameters. Kindly help.
...
0
votes
0
answers
746
views
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
(
...
0
votes
1
answer
26
views
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 ...
-1
votes
1
answer
526
views
Using SQL data as the data source for an Excel pivot table when the SQL uses an SQL Table Valued function
Using Microsoft Query I'm trying to setup a sql data source for an Excel pivot table, specifically when the sql contains a table valued function. The sql shown in the picture below is how it ...
- 317
0
votes
0
answers
396
views
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 ...
0
votes
1
answer
794
views
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 ...
- 43
0
votes
2
answers
1k
views
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 ...
- 23
0
votes
0
answers
45
views
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) ...
- 1
1
vote
1
answer
475
views
Multiple Parameters in UDF in SQL
I am trying to established a Function to have multiple parameters as see below @SITENAME, @MARKET and @DATE.
ALTER FUNCTION [dbo].[Platform_Parser]
(
@SITENAME NVARCHAR(2000) NULL
,@MARKET ...
- 11
1
vote
1
answer
749
views
Table valued parameter has wrong data
I have ASP .NET Web Form application with SignalR handling online notifications. Somewhere down the road of simplifying the code, I ran into an issue when stored procedure accepting whole table of ...
- 31
0
votes
1
answer
461
views
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 ...
1
vote
1
answer
791
views
Stored procedure table-valued variable without aliases in query string must declare scalar variable
I will pass a table-valued input parameter into a stored procedure, and also a variable that contains query string, so I made my sproc like this.
CREATE PROCEDURE [dbo].[SP_SelectData_View]
(
@...
- 43
0
votes
1
answer
362
views
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
...
- 300
0
votes
0
answers
239
views
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....
- 15.3k
0
votes
1
answer
210
views
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 ...
2
votes
0
answers
233
views
Is it in R possible to use a user-defined-table-type (UDT) existing in the database to execute a stored procedure with a parameter based on this UDT?
Is it possible in R (as in other languages) to do this with R-database-functionality:
my database contains stored procedures which use user-defined types (UDT, especially table-types) for some ...
- 21
1
vote
1
answer
2k
views
Execution timeout expiry when executing a stored procedure which uses a table valued parameter
I have a stored procedure which uses a table valued parameter (tvp). In my application, I use a datatable as a SqlParameter to match the tvp structure. The problem is that it sometimes takes 25 ...
- 31
0
votes
0
answers
547
views
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 ...
- 23.4k
0
votes
1
answer
279
views
Inner join if tables are empty with Top 1000 results
I have three table valued parameters passed into an SP. These all contain data to filter a query. I want to join them to a table as below but only if there is data in a table valued parameter
...
- 1,024
-1
votes
1
answer
2k
views
Inserting data into table from table-valued parameter
I am trying to create a stored procedure to which is passed a TVP and then some data from the TVP is inserted into two tables.
I have already implemented the stored procedure, but only the second ...
- 21
0
votes
1
answer
973
views
How to access columns and data of a Sql parameter of SqlDbType as Structured in C#?
In C#, for Table-valued parameter I add a SqlParameter with 'SqlDbType' as 'Structured' and 'Value' as a C# DataTable.
I want to extract this data later in my code.
I want to verify if the SqlDbType/...
- 3
0
votes
1
answer
2k
views
How to pass a user defined table type returned from a function as a parameter to another inline function (without 'DECLARE')?
I'm afraid the answer to this is "NO, you can't", but since I can't find this explained anywhere I'm gonna ask anyway.
Given a user defined table type:
CREATE TYPE MyType AS TABLE(
[Id] ...
- 57.5k
0
votes
1
answer
3k
views
Pyodbc Error "A TVP's rows must be Sequence objects.", 'HY000'
I am calling an API and moving the data from json to sql. When I try to post to sql I get this error:
Pyodbc Error "A TVP's rows must be Sequence objects.", 'HY000'
Here is my SQL Statement ...
4
votes
1
answer
3k
views
Query to List Table-Valued Functions and their Input Arguments?
The following query will return a list of all table-valued functions in a database and a list of their output columns (and column data types).
USE DATABASENAME
GO
SELECT
rc.*
FROM
INFORMATION_SCHEMA....
- 5,285
0
votes
0
answers
1k
views
How to pass a df as table-valued parameter, and once df passed as TPV using that update or insert the output table in SQL Server
As part of my development activity in project I need to update or insert the output table. I am trying to pass a DF as table valued parameter in store procedure. By using a stored procedure, I want to ...
- 13
1
vote
1
answer
691
views
SQL server TVP bulk insert more than 2000 params
CREATE TABLE EAddressDetails (EAddress varchar(100) NULL))
CREATE TYPE EAddressList AS TABLE (Email VARCHAR(100)))
CREATE PROCEDURE EAddresses_InsertBatch
@EAddressBatch [EAddressList] READONLY
AS
...
- 237
0
votes
0
answers
36
views
Is passing record to SQL Server from external application equally expensive than passing it within the SQL Server
So I have a stored procedure in which I pass user defined table type parameter from my C# web application.
I pass thousands of them.
Now within the stored procedure I modify this data a bit and so I ...
- 9,046
0
votes
1
answer
469
views
Does FreeTDS ODBC + pyodbc support table-valued parameters (TVPs)?
I'm working on unix/rhel7 system. I have installed require drivers for FreeTDS, unixODBC and pyodbc.Other query is working fine but when I'm trying execute stored proc with TVP (table valued parameter)...
- 41
2
votes
1
answer
2k
views
'Must Declare the Scalar Variable' Error When Passing a Table-Valued Parameter to a Parameterized SQL Statement
In C# I'm trying to pass a DataTable as a parameter to an SQL statement. The code I have is below:
protected virtual void DoDeleteRecords(List<Guid> ids)
{
if (ids.Count > 0)
{
...
- 349
1
vote
2
answers
840
views
Wrapper TSQL Table-valued Function is slow
I built a wrapper function that currently just calls another table-valued Function but it just added a huge amount to the execution time as Client processing time. Is there a faster way to do this?
...
- 287
0
votes
1
answer
290
views
WHERE IN performs much better with Table Type than with hardcoded values in SQL Server
I have 2 queries that are essentially the same (at least if I didn't miss something).
DECLARE @siloIds SiloIdsTableType
INSERT INTO @siloIds VALUES
(1),(2),(3)
-- Query 1
SELECT *
FROM [Transaction]
...
- 30.9k
0
votes
0
answers
431
views
Error SQL71501: Error validating element on a stored procedure
We have a stored procedure which calls a table valued function like this
DECLARE @PID INT
DECLARE @PCost DECIMAL(30,15)
SELECT @ID = PID, @PCost = Cost
FROM [dbo].[myfunction] (@param1, @param2)
...
- 2,133