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
1
vote
0 answers

How to execute the Update method using a variable to call the table?

I'm trying to create a procedure in SQL Server to allow me to update a column on a table. However, I want the table name to come from a variable instead of being given by me, but I get an error message. Here's what I have so far: I have a form in an…
risan
  • 11
  • 2
1
vote
2 answers

Trying to use a variable in a SQL WHERE IN statement

I'm trying to use a @List Table to feed a Where IN statement. I keep getting the error, "Must Declare the scalar variable "@List". I know I can simply use the select statement in the WHERE IN part, but I'm needing to find out conceptually if this…
Jonathan Peters
  • 31
  • 7
1
vote
1 answer

Different results while inserting into temp table and table variable

When I'm using a table variable to store queried result like this: INSERT INTO @sortedArticleIds SELECT article_id, NULL AS groupBy FROM #articleIds a GROUP BY article_id ORDER BY MIN(sortBy) DESC; the rows inserted to…
Chinthaka Fernando
  • 744
  • 8
  • 12
1
vote
1 answer

Inner join two table variables and return an output cursor from an anonymous plsql block in a c# application

I have this scenario: I need to insert the result of many joins (15 tables with various filters) into a table variable var_TB_PROJECT Then insert the result of many other joins (8 tables with various filters) into another table variable…
Goldar
  • 61
  • 1
  • 6
1
vote
2 answers

How to create a sql server table variable in a query using sqlalchemy in python

I'm trying to create a table variable in SQL Server, query it, and return the results to a pandas dataframe (see example). I want to do this so that I can aggregate data in the database prior to sending it to a pandas dataframe. I recall that…
mnky9800n
  • 1,113
  • 2
  • 15
  • 33
1
vote
1 answer

How to use table variable in dynamic sql? OR create temporary table from user defined table type?

I am using SQL Sever 2016 and I have created user-defined table-type as below: CREATE TYPE [dbo].[UDTT_Items] AS TABLE( [ItemId] int identity(1, 1), [ItemCode] [varchar](10) NULL, [ItemName] [varchar](255) NULL, [StockQty]…
M_Idrees
  • 2,080
  • 2
  • 23
  • 53
1
vote
3 answers

Using CONTAINS to find items IN a table

I'm trying to write a SP that will allow users to search on multiple name strings, but supports LIKE functionality. For example, the user's input might be a string 'Scorsese, Kaurismaki, Tarkovsky'. I use a split function to turn that string into…
Julian Drago
  • 719
  • 9
  • 23
1
vote
1 answer

Input and output a table from a function in SQL

I would like to create a function in T-SQL which both inputs and outputs instances of a user-defined table type. However, on the line of the RETURN statement, it is throwing an error: Must declare the scalar variable @output_table My code: CREATE…
Eric Ma
  • 91
  • 1
  • 5
1
vote
0 answers

Locking of temp table in SPROC from multithreaded application

I have a scenario where a Multithreaded .net application calls a stored procedure which in turn uses a temp table in it. In our application concurrently we are calling four threads which individually triggers the same stored procedure. In this…
Prakazz
  • 421
  • 1
  • 8
  • 21
1
vote
1 answer

Must declare the scalar variable when referencing a table valued parameter

This question follows from this one. The following SQL works: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Update_Repair_Details] @RepairID BIGINT, @NewDetails NewDetails READONLY AS BEGIN SET NOCOUNT ON; …
SezMe
  • 527
  • 8
  • 24
1
vote
0 answers

TSQL: Loop through a table by given time intervals and count

Source table dbo.sourcetable The table has more columns than shown here: | ID | TrackingID | TrackingTime |.... |--------|----------------|-----------------------| | 001 | 10 |2017-03-08 10:12:20.240| | 003 | …
Phil
  • 113
  • 3
1
vote
1 answer

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time, cannot free up more resiources

I'm trying to run a stored procedure. In the SP I create two table variables (by using 3 CTE's). Then join the two tables and INSERT INTO an existing table. I keep getting the error CANNOT OBTAIN A LOCK RESOURCE. Any ideas how I can fix this? …
solarissf
  • 1,199
  • 2
  • 23
  • 58
1
vote
2 answers

Loading CSV file data into a Table Variable (Index-by table) in Oracle

My requirement is that I need to read the CSV file data and query it with one existing table in the Database to update some records. One approach I thought that to create a new table (temp) and load the CSV file into that table and query that with…
user1547554
  • 441
  • 2
  • 8
  • 16
1
vote
0 answers

"Must declare the scalar variable @..." error when the variable is used in CTE

I'm splitting three columns into multiple multiple addresses (each column has more than 1 value separated by the ","). I've written the code below, but it throws me and error: Must declare the scalar variable '@tblAddresses'. I does work coping…
Data Engineer
  • 795
  • 16
  • 41
1
vote
1 answer

Update Table Variable in SQL Server and getting Must declare the scalar variable

I CAN change to temp table if need be, but when I am doing an UPDATE on a table variable in sql server, why are I getting this error and how can I fix, should I switch to temp table ? Must declare the scalar variable…
user6321478
1 2 3
…
11 12