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

Not able to access Table Valued Parameter for queries in sql

I have two queries in the single stored procedure. I have one table table valued parameter & that TVP I am using for both the queries. One of my query is working fine (Update Query is working fine) but If I am using that same TVP for the insert…
Hardik Parmar
  • 1,053
  • 3
  • 15
  • 39
0
votes
1 answer

SQL Table Variables - adding constraint that checks for existence of matching row set

Updating with pmbAustin's suggestion pmbAustin, thanks. I think that will work. I've created a function "dbo.CK_WinteamSportExists" that returns a 0 or 1. However I'm now getting a baffling error message. When I tested my solution I get the…
user2577010
  • 41
  • 1
  • 5
0
votes
1 answer

How to store result of cross-table-query to a table variable

I actually found something here but I need a syntax for multiple tables. Example: DECLARE CURSOR select_results IS SELECT T1.ID, T2.DP FROM T1, T2 WHERE T1.ROW_ID=T2.DP; select_result T1.ID%ROWTYPE, T2.DP%ROWTYPE; BEGIN -- DO…
nenito
  • 1,214
  • 6
  • 19
  • 33
0
votes
1 answer

Cannot call methods on table ? Table Variable

I am trying to put outer apply on the table varible but I am getting error like below Cannot call methods on table. I have Split function which split the string to certain length CREATE FUNCTION Split(@String varchar(MAX), @SplitLength int)…
sandeep_jagtap
  • 1,484
  • 2
  • 17
  • 24
0
votes
0 answers

DECLARE statement make variables to be global?

Why DECLARE statement is making declared variables to be global? For example: SET NOCOUNT ON GO DECLARE @LoopCounter TINYINT = 3 WHILE @LoopCounter <> 0 BEGIN DECLARE @TempDataSource TABLE ([ID] TINYINT) SELECT [ID] …
gotqn
  • 42,737
  • 46
  • 157
  • 243
0
votes
0 answers

Huge performance difference between table variables and temp tables

I have an UDF, providing a bunch of data. Several table variables are used. At this time, no indices are created. Runtime with testdata is about 30 sec. Only changing the table variables into temp tables ( out of the UDF, since #tables are not…
Nico
  • 1,175
  • 15
  • 33
0
votes
1 answer

With IN Clause when Passing Collection as Parameter In Sql Server

Query Gives O/p as ItemSizeNM (colName) 'U','V','X','Y' But when I used this as input to IN Query in the Code Which I did. It Doesn't Gives same resultset. Why This Happens...? Table MstItemSize Has Proper Data. declare @tblRingSize table (…
user2035827
0
votes
1 answer

SQL Server IF..ELSE do not show at Windows Application

I have this stored procedure: exec sp_Defect_B '2013-05-20 00:00:00','2013-05-25 23:59:59' Which has a IF..ELSE to execute different thing depends on the code given: alter proc [dbo].[p_Defect_B] (@dtFrom datetime, @dtTo datetime) as …
Arnold Gunawan
  • 35
  • 4
0
votes
1 answer

define table argument as OUTPUT in PROC

define table argument as OUTPUT in PROC how can i define table argument as OUTPUT in PROC and don't READONLY? if it's not possible, is there another way to do same this? thanks!
BlackViking
  • 53
  • 1
  • 8
0
votes
1 answer

Stored Procedure that has table argument in T-SQL

Table Argument as OUTPUT I want to pass a table variable into a procedure that has table argument as output, but not as read only! I want to be able to modify that argument inside the PROC. Is this possible? If it's not possible, is there another…
BlackViking
  • 53
  • 1
  • 8
0
votes
4 answers

Optimize multiple joins with table functions

I would like to join several times with the same table function for different input variables in the same query. But this turns in my case out to be much slower than using table variables and selecting from the table functions separately. How can I…
Ole Lynge
  • 4,457
  • 8
  • 43
  • 57
0
votes
0 answers

Is it possible to iterate through a table variable using a cursor?

In SQL Server 2005, is it possible to iterate through a table variable using a cursor?
Guillermo Gutiérrez
  • 17,273
  • 17
  • 89
  • 116
0
votes
2 answers

sql server update many columns with a select from a join of a table variable and a db table

I have the classical person -> person attributes scheme. So, like this: person(PK) <- person_attribute(FK) What I need is a query to get one row where a person is joined with her attributes. For example, to transform: Person: { ID = 123456, Name =…
h.alex
  • 902
  • 1
  • 8
  • 31
0
votes
1 answer

table variable self reference in SQL server 2008

I found a very strange thing in SQL server. I am not sure it is normal. insert @table select ID, Lastname from tableB where ID NOT IN ( select ID from @Table) It always says 0 row inserted. If I use a physical table instead of @table, it works…
user1733394
  • 9
  • 2
0
votes
1 answer

Table Valued Parameter: sending data in small chunks

I am reading from a csv file and sending data as table variable to a stored procedure. From what i have tested so far , I am able to process 300k records in 3 mins 30 seconds . The file may contain up to millions of records as we go. I wanted to…
user1110790
  • 787
  • 2
  • 8
  • 27
1 2 3
…
11
12