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

Question

I have a table variable in a script  not a stored procedure    Two questions    How do I drop the table variable   Drop Table  varName gives an  Incorrect snytax  error  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

User · Answer

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

User · Answer

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

User · Answer

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  gt   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  gt   2021-01-01         SELECT     FROM  recent records      ROLLBACK

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to table-variable