我通常在存储过程的开头和结尾放置以下这些语句。
这是一个用于检查#temp表是否存在的"exists"检查。
IF OBJECT_ID('tempdb..#MyCoolTempTable') IS NOT NULL
begin
drop table
end
完整示例:
(注意,没有任何“SELECT INTO”语句)
CREATE PROCEDURE [dbo].[uspTempTableSuperSafeExample]
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#MyCoolTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyCoolTempTable
END
CREATE TABLE #MyCoolTempTable (
MyCoolTempTableKey INT IDENTITY(1,1),
MyValue VARCHAR(128)
)
INSERT INTO #MyCoolTempTable (MyValue)
SELECT LEFT(@@VERSION, 128)
UNION ALL SELECT TOP 3 LEFT(name, 128) FROM sysobjects
INSERT INTO #MyCoolTempTable (MyValue)
SELECT TOP 3 LEFT(name, 128) FROM sysobjects ORDER BY NEWID()
ALTER TABLE #MyCoolTempTable
ADD YetAnotherColumn VARCHAR(128) NOT NULL DEFAULT 'DefaultValueNeededForTheAlterStatement'
INSERT INTO #MyCoolTempTable (MyValue, YetAnotherColumn)
SELECT TOP 3 LEFT(name, 128) , 'AfterTheAlter' FROM sysobjects ORDER BY NEWID()
SELECT MyCoolTempTableKey, MyValue, YetAnotherColumn FROM #MyCoolTempTable
IF OBJECT_ID('tempdb..#MyCoolTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyCoolTempTable
END
SET NOCOUNT OFF;
END
GO
输出示例:
1 Microsoft-SQL-Server-BlahBlahBlah DefaultValueNeededForTheAlterStatement
2 sp_MSalreadyhavegeneration DefaultValueNeededForTheAlterStatement
3 sp_MSwritemergeperfcounter DefaultValueNeededForTheAlterStatement
4 sp_drop_trusted_assembly DefaultValueNeededForTheAlterStatement
5 sp_helplogreader_agent DefaultValueNeededForTheAlterStatement
6 fn_MSorbitmaps DefaultValueNeededForTheAlterStatement
7 sp_check_constraints_rowset DefaultValueNeededForTheAlterStatement
8 fn_varbintohexstr AfterTheAlter
9 sp_MSrepl_check_publisher AfterTheAlter
10 sp_query_store_consistency_check AfterTheAlter
此外,您还可以查看我的答案(关于“#temp表的范围是什么”):https://dev59.com/0GIj5IYBdhLWcg3wk182#20105766
DROP TABLE IF EXISTS #yourTable;
可能会有所帮助。 - Hart CO