即使我正在检查和删除临时表，数据库错误消息中已经有一个名为“#dirs"的对象 [英] There is already an object named '#dirs' in the database error message even though I'm checking and dropping the temp table

问题描述

如果我正在检查对象是否存在，我不确定为什么我一直收到对象已经存在的消息，如果存在，我会删除它.有人有线索吗?

I'm not sure why I keep getting the object already exists message if I'm doing the check whether the object exists and I'm dropping it if it does. Anyone have any clues?

错误信息:

消息 2714，级别 16，状态 1，过程 spCreateDirectoryStructure，第 54 行数据库中已经有一个名为#dirs"的对象.消息 2714，级别 16，状态 1，过程 spCreateDirectoryStructure，第 74 行数据库中已经有一个名为#dirs"的对象.

Msg 2714, Level 16, State 1, Procedure spCreateDirectoryStructure, Line 54 There is already an object named '#dirs' in the database. Msg 2714, Level 16, State 1, Procedure spCreateDirectoryStructure, Line 74 There is already an object named '#dirs' in the database.

存储过程:

CREATE PROCEDURE spCreateDirectoryStructure
AS 
       BEGIN
             SET NOCOUNT ON;

             DECLARE @year CHAR(4)
             DECLARE @month VARCHAR(2)
             DECLARE @day VARCHAR(2)
             DECLARE @root VARCHAR(200)
             DECLARE @dir VARCHAR(200)
             DECLARE @yearDir VARCHAR(200)
             DECLARE @monthDir VARCHAR(200)
             DECLARE @dayDir VARCHAR(200)

             SET @root = 'C:\Test\'
             SET @year = DATEPART(YEAR, GETDATE())
             SET @month = DATEPART(MONTH, GETDATE())
             SET @day = DATEPART(DAY, GETDATE())
             SET @yearDir = @root + @year + '\'
             SET @monthDir = @root + @year + '\' + @month + '\'
             SET @dayDir = @root + @year + '\' + @month + '\' + @day + '\'

            -- check root folder for year folder
             IF OBJECT_ID(N'tempdb..#dirs') IS NOT NULL 
                BEGIN 
                      DROP TABLE #dirs
                END

             CREATE TABLE #dirs (Directory VARCHAR(200))

             INSERT INTO #dirs
                    EXEC master.dbo.xp_subdirs 
                        @root


             IF NOT EXISTS ( SELECT Directory
                             FROM   #dirs
                             WHERE  Directory = @year ) 
                EXEC master.sys.xp_create_subdir 
                    @yearDir

            -- **********************************************
            -- check year folder for month folder

             IF OBJECT_ID(N'tempdb..#dirs') IS NOT NULL 
                BEGIN
                      DROP TABLE #dirs
                END
             CREATE TABLE #dirs (Directory VARCHAR(200))

             INSERT INTO #dirs
                    EXEC master.dbo.xp_subdirs 
                        @yearDir


             IF NOT EXISTS ( SELECT Directory
                             FROM   #dirs
                             WHERE  Directory = @month ) 
                EXEC master.sys.xp_create_subdir 
                    @monthDir

            -- **********************************************
            -- check month folder for day folder

             IF OBJECT_ID(N'tempdb..#dirs') IS NOT NULL 
                BEGIN
                      DROP TABLE #dirs
                END
             CREATE TABLE #dirs (Directory VARCHAR(200))

             INSERT INTO #dirs
                    EXEC master.dbo.xp_subdirs 
                        @monthDir


             IF NOT EXISTS ( SELECT Directory
                             FROM   #dirs
                             WHERE  Directory = @day ) 
                EXEC master.sys.xp_create_subdir 
                    @dayDir
       END
GO

推荐答案

#dirs 在编译存储过程时存在.

The #dirs exists when the stored procedure is compiled.

只需删除表，然后您就可以创建存储过程.

Just remove the table and then you can create the stored procedure.

既然你正在创建表，考虑将它声明为局部变量:

Since you are creating the table anyway, consider declaring it as a local variable:

declare @dirs table (directory varchar(200))

局部变量超出作用域时(存储过程退出时)会自动删除.那么你就不用担心清理临时目录了.

The local variable will be removed automatically when it goes out of scope (when the stored procedure exits). Then you don't have to worry about cleaning up the temp directory.

这篇关于即使我正在检查和删除临时表，数据库错误消息中已经有一个名为“#dirs"的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！