Creating table variable in SQL server 2008 R2

Question

what is table variable? And how to create a table variable (virtual in-memory table) with columns that match the existing Stored procedure resultset.

I executed the procedure and after executing it, the column names are known to me. But do i have to declare the same data type of the columns as it was in stored procedure?

EDIT: I tried this

DECLARE @Table TABLE( 
name varchar(30) NOT NULL, 
location varchar(30) NOT NULL 
); 

INSERT @Table 
SELECT name, location FROM 
Exec SPROC @param , @param

Just a word of warning: A table variable isn't guaranteed to be in memory. That's a myth. — JohnFx
– JohnFx, Commented Mar 13, 2012 at 5:13
Can you include the code you have so far so we can answer your question? — JohnFx
– JohnFx, Commented Mar 13, 2012 at 5:14
@JohnFx I havnn't executed code for the creation of table variable. I made a stored procedure and after executing it i got some column names, now i want those column names to be stored in table variable. And if it is not always in memory then how can i find it in my database, Sorry for troubling i am new on SQL. Please Help. — PankajMishra
– PankajMishra, Commented Mar 13, 2012 at 5:20
To clarify: It might be on disk (depending on a number of factors) instead of memory, but that doesn't mean it will be a table that you can query directly. If you need that functionality go with a temp table instead of a table variable. — JohnFx
– JohnFx, Commented Mar 13, 2012 at 15:09
possible duplicate of SQL Server - SELECT FROM stored procedure — JohnFx
– JohnFx, Commented Mar 13, 2012 at 15:13

Martin Smith · Accepted Answer · 2013-12-11 21:19:54Z

@tableName Table variables are alive for duration of the script running only i.e. they are only session level objects.

To test this, open two query editor windows under sql server management studio, and create table variables with same name but different structures. You will get an idea. The @tableName object is thus temporary and used for our internal processing of data, and it doesn't contribute to the actual database structure.

There is another type of table object which can be created for temporary use. They are #tableName objects declared like similar create statement for physical tables:

Create table #test (Id int, Name varchar(50))

This table object is created and stored in temp database. Unlike the first one, this object is more useful, can store large data and takes part in transactions etc. These tables are alive till the connection is open. You have to drop the created object by following script before re-creating it.

IF OBJECT_ID('tempdb..#test') IS NOT NULL
  DROP TABLE #test

Hope this makes sense !

Collectives™ on Stack Overflow

Creating table variable in SQL server 2008 R2

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related