How do I drop table variables in SQL-Server? Should I even do this?

sql sql-server table-variable
jtpereyda picture jtpereyda · Apr 13, 2011 · Viewed 131.2k times · Source

I have a table variable in a script (not a stored procedure). Two questions:

  1. How do I drop the table variable? Drop Table @varName gives an "Incorrect snytax" error.
  2. Should I always do this? I hear it's a good practice. Is it ever really necessary for small scripts like this?

Here's my code:

Declare @projectList table(
    name varchar(40) NOT NULL);

Insert Into @projectList
Values ('BCR-00021')

Select *
From @projectList

Drop Table @projectList -- does not work

Answer

Hogan picture Hogan · Apr 13, 2011

Table variables are automatically local and automatically dropped -- you don't have to worry about it.

Related questions

Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?

Read More
How to use table variable in a dynamic sql statement?

In my stored procedure I declared two table variables on top of my procedure. Now I am trying to use that table variable within a dynamic sql statement but I get this error at the time of execution of that …

Read More
Can I loop through a table variable in T-SQL?

Is there anyway to loop through a table variable in T-SQL? DECLARE @table1 TABLE ( col1 int ) INSERT into @table1 SELECT col1 FROM table2 I use cursors as well, but cursors seem less flexible than table variables. DECLARE cursor1 CURSOR FOR …

Read More