I have a table variable in a script (not a stored procedure). Two questions:
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
This question is related to
sql
sql-server
table-variable
Temp table variable is saved to the temp.db and the scope is limited to the current execution. Hence, unlike dropping a Temp tables e.g drop table #tempTable, we don't have to explicitly drop Temp table variable @tempTableVariable. It is automatically taken care by the sql server.
drop table @tempTableVariable -- Invalid
But you all forgot to mention, that if a variable table is used within a loop it will need emptying (delete @table) prior to loading with data again within a loop.
Indeed, you don't need to drop a @local_variable
.
But if you use #local_table
, it can be done, e.g. it's convenient to be able to re-execute a query several times.
SELECT *
INTO #recent_records
FROM dbo.my_table t
WHERE t.CreatedOn > '2021-01-01'
;
SELECT *
FROM #recent_records
;
/*
can DROP here, otherwise will fail with the following error
on re-execution in the same window (I use SSMS DB client):
Msg 2714, Level ..., State ..., Line ...
There is already an object named '#recent_records' in the database.
*/
DROP TABLE #recent_records
;
You can also put your SELECT statement in a TRANSACTION to be able to re-execute without an explicit DROP:
BEGIN TRANSACTION
SELECT *
INTO #recent_records
FROM dbo.my_table t
WHERE t.CreatedOn > '2021-01-01'
;
SELECT *
FROM #recent_records
;
ROLLBACK
if somebody else comes across this... and you really need to drop it like while in a loop, you can just delete all from the table variable:
DELETE FROM @tableVariableName
Just Like TempTables, a local table variable is also created in TempDB. The scope of table variable is the batch, stored procedure and statement block in which it is declared. They can be passed as parameters between procedures. They are automatically dropped when you close that session on which you create them.
Here is a solution
Declare @tablename varchar(20)
DECLARE @SQL NVARCHAR(MAX)
SET @tablename = '_RJ_TEMPOV4'
SET @SQL = 'DROP TABLE dbo.' + QUOTENAME(@tablename) + '';
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N'U'))
EXEC sp_executesql @SQL;
Works fine on SQL Server 2014 Christophe
Table variables are just like int or varchar variables.
You don't need to drop them. They have the same scope rules as int or varchar variables
The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.
Source: Stackoverflow.com