如何删除 SQL-Server 中的表变量？我应该这样做吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5653520/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I drop table variables in SQL-Server? Should I even do this?
提问by jtpereyda
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?
- 如何删除表变量？删除表 @varName 给出了“不正确的 snytax”错误。
- 我应该总是这样做吗？我听说这是一个很好的做法。像这样的小脚本真的有必要吗？
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
回答by Hogan
Table variables are automatically local and automatically dropped -- you don't have to worry about it.
表变量自动本地化并自动删除——您不必担心。
回答by gbn
Table variables are just like int or varchar variables.
表变量就像 int 或 varchar 变量。
You don't need to drop them. They have the same scoperules as int or varchar variables
你不需要放弃它们。它们具有与int 或 varchar 变量相同的作用域规则
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.
变量的范围是可以引用该变量的 Transact-SQL 语句的范围。变量的作用域从它被声明的那一刻开始，直到它被声明的批处理或存储过程结束。
回答by Leo Much
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
回答by Peppe
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.
但是你们都忘了提到，如果在循环中使用变量表，则需要在循环中再次加载数据之前清空（删除@table）。
回答by Gagan
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.
就像 TempTables 一样，也在 TempDB 中创建了一个局部表变量。表变量的作用域是声明它的批处理、存储过程和语句块。它们可以作为过程之间的参数传递。当您关闭创建它们的会话时，它们会自动删除。
回答by CSonneck
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
在 SQL Server 2014 Christophe 上运行良好
