Stack Overflow
Stack Overflow

Questions tagged [table-variable]

A SQL Server local variable that can store rows.

From MSDN:

Is a special data type that can be used to store a result set for processing at a later time. table is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function.

A table variable differs from a "temporary table" in that it has

  • no statistics
  • limited index options
  • execution plans often assume single row
  • does not participate in user transactions
  • for the batch/proc scope only
170 questions
0
votes
1 answer

Oracle put resultset into variable in FORALL

I have the following plsql block declare TYPE t_mds_ids IS TABLE OF mds.id%TYPE; l_mds_ids t_mds_ids; l_mds_parents t_mds_parents; begin SELECT id BULK COLLECT INTO l_mds_ids FROM mds; FORALL indx IN l_mds_ids.FIRST ..…
MaterialGirl
  • 363
  • 2
  • 10
  • 22
0
votes
1 answer

Must declare the table variable error while creating a TVP function

I have the following function created with a TVP. I am inserting data initially into the TVP and then selecting from it. CREATE FUNCTION [dbo].employees_data( @employeeIds NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) -- WITH SCHEMABINDING AS BEGIN …
Sagar Acharya
  • 1,763
  • 2
  • 19
  • 38
0
votes
0 answers

How to update table variable in stored procedure from another user defined function in SQL Server?

Scenario: user-defined function returns one table. If that table contains at least one row, one column in the table variable from another stored procedure should get updated. Should I use cross join on UDF to update table variable?
Snehal Sawant
  • 1
0
votes
1 answer

Assigning TABLE typed variables in T-SQL

I've defined a user defined Table type - call it TrackRefsTable Having declared two variables DECLARE @FOO1 AS TrackRefsTable DECLARE @FOO2 AS TrackRefsTable Is there any way to set one to t'other? The obvious SET @FOO2 = @FOO1 doesn't work as this…
user6499401
0
votes
2 answers

Conditional IF Exists Insert

I have a query to first check if something exists. If yes, insert something into a table variable. Otherwise, insert something else into the same table variable. IF NOT EXISTS (SELECT 1 FROM @Main WHERE CustomerId = @CustomerId) ( BEGIN …
ichachan
  • 637
  • 1
  • 10
  • 34
0
votes
1 answer

PLSQL ORACLE : Inner join between table variables

I need to create two table-type variables in oracle and make inner join between them. I can't create temporary table in the source database because I dont have privileges. How to create an anonymous plsql block in oracle something relative to this…
Goldar
  • 61
  • 1
  • 6
0
votes
2 answers

How to use a Table Variable with this specific T-SQL query?

I am using SQL Server 2012 and I have the following T-SQL query running against a table on my database. The query runs fine but my issue is that I want to get an output for several specific dates. Instead of running the query multiple times (by…
user3115933
  • 4,303
  • 15
  • 54
  • 94
0
votes
1 answer

SQL Server / Delphi : how to transfer a list of integers as a parameter using a table variable type?

I'm using DELPHI XE6 with FireDAC components and has a SQL Server database server. In the database, I have a stored procedure that takes two parameters, one integer and then a table variable that takes just one columns of type integer. The table…
larand
  • 773
  • 1
  • 9
  • 26
0
votes
1 answer

Adding new data to the existing tables of a database in SQL Server using table variables

I'm trying to add new rows to the existing tables in the database by using table variables. The problem I'm facing is due to the auto-increment primary key in the database. The tables are like below: Table 1: State - ID_State(PK) | state …
schikkamksu
  • 75
  • 1
  • 12
0
votes
1 answer

Why are my Memory-Optimized Table Variables Using Disk Space

I have created a memory optimized table type in my database like so: create type tblLocation_InMem as table( IndexLocation int not null index IX_LocInMem, IndexRootLocation int not null, fldLocCrumbs nvarchar(1024) not null ) with…
Ian
  • 4,169
  • 3
  • 37
  • 62
0
votes
0 answers

Can table "variables" be assigned in SQL Server?

Suppose I have a table variable defined as: DECLARE @FilteredIDs TABLE(ID UNIQUEIDENTIFIER, UNIQUE CLUSTERED (ID)) Functions can return table variables, so supposed I have a function that accepts some input and returns @OutputTable (with identical…
Triynko
  • 18,766
  • 21
  • 107
  • 173
0
votes
0 answers

Create Table Variable only when there is rows to insert in it

In Query that haves 3 tables variables joined with 2 tables in some cases where those variable tables are empty (no rows) in the Execution Plan still appears with 16% cost for the scan of each table variable. From searching found this rule If a…
H.C
  • 565
  • 7
  • 28
0
votes
3 answers

How to use an And-Or as a variable

I'm actually trying to get the data from a table but I wanted that "And/Or" changes based on the table. I'm using SQL Server. I'm into something like this: DECLARE @TURNOS TABLE (ID INT IDENTITY(1, 1), INITURNO INT, …
Falakienos
  • 107
  • 1
  • 5
0
votes
1 answer

Sql Server 2016 Temp Table or Table Variable inside WHILE Loop

I have a while loop, that will run 300M times. Each time this loop runs, it creates a 1 row table with two columns, which in the end of the loop this table is deleted and once the loop re-starts, this table is rebuilt again with 1 row with two…
wr_lcb_ck
  • 85
  • 2
  • 9
0
votes
1 answer

Declare and set a variable with more than one possible value

Is it possible to set a variable to have more than one value? In the example below, I would like to set the @variable and use store numbers 1,4,7 and 12 And then use the variable in the WHERE statment. The below is purely an example to see if this…
PeterH
  • 975
  • 2
  • 14
  • 36
1 2 3
…
11 12