SQL存储过程中#MyTempTable无效对象名问题求助

你的问题根源在于局部临时表的作用域限制：

• 局部临时表（#开头）仅在创建它的会话/作用域内可见。
• 你通过EXEC (@SQL)执行动态SQL时，这段代码会在一个独立的子批次（sub-batch）中运行，子批次创建的#MyTempTable在主存储过程的作用域里完全不可见。当子批次执行完毕，这个临时表会被自动销毁，所以主存储过程里的IF EXISTS(SELECT 1 FROM #MyTempTable)自然会报“找不到对象”的错误。

而之前使用SearchTMP全局表的并发问题，是因为全局表（无#前缀）是所有会话共享的，多个用户同时执行时会出现表已存在的冲突。

我推荐两种优化方向，优先选择方案一，既解决问题又提升性能：

方案一：优化逻辑，完全移除临时表（推荐） #

观察你的代码，你其实不需要存储匹配的完整数据，只是需要判断目标表中是否存在符合条件的行。直接通过动态SQL返回行数即可，完全规避临时表的作用域问题：

修改步骤 #

1. 调整@SQLTbl中SQLStatement的生成逻辑，改为统计行数：

UPDATE @SQLTbl 
SET SQLStatement = 'SELECT COUNT(1) FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)

2. 在执行动态SQL时，用sp_executesql获取返回的行数，判断是否大于0：

DECLARE @RowCount INT -- 新增变量存储行数
WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
BEGIN 
    SELECT TOP 1 @tmpTblname = Tablename , @SQL = SQLStatement 
    FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0 

    IF @GenerateSQLOnly = 0 
    BEGIN 
        -- 用sp_executesql获取行数
        EXEC sp_executesql @SQL, N'@RowCount INT OUTPUT', @RowCount OUTPUT
        IF @RowCount > 0 
        BEGIN 
            SELECT @MatchFound = 1 
            INSERT INTO @output (Id, Name) 
            Select * from [DynaForms].[dbo].[Enums_Tables] 
            where id = parsename(@tmpTblname,1) -- 单个表名用=替代IN更高效
        END 
    END 
    -- 其余代码保持不变
END

方案二：使用会话唯一的全局临时表（兼容原有逻辑） #

如果你需要保留存储匹配数据的逻辑，可以给全局临时表加上会话ID后缀，确保每个用户的临时表唯一：

修改步骤 #

1. 生成会话唯一的临时表名：

DECLARE @UniqueTempTable NVARCHAR(100) = '##SearchTMP_' + CAST(@@SPID AS NVARCHAR(10))

2. 调整动态SQL的生成逻辑：

UPDATE @SQLTbl 
SET SQLStatement = 'SELECT * INTO ' + @UniqueTempTable + ' FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)

3. 执行时清理当前会话的临时表，并使用唯一表名操作：

IF @GenerateSQLOnly = 0
BEGIN
    -- 清理当前会话的临时表
    IF OBJECT_ID('tempdb..' + @UniqueTempTable,'U') IS NOT NULL
        EXEC('DROP TABLE ' + @UniqueTempTable)
    EXEC (@SQL)
    IF EXISTS(SELECT 1 FROM tempdb..' + @UniqueTempTable)
    BEGIN
        SELECT @MatchFound = 1
        INSERT INTO @output (Id, Name)
        Select * from [DynaForms].[dbo].[Enums_Tables] 
        where id in (SELECT parsename(@tmpTblname,1) FROM ' + @UniqueTempTable)
    END
    -- 执行完毕清理临时表
    IF OBJECT_ID('tempdb..' + @UniqueTempTable,'U') IS NOT NULL
        EXEC('DROP TABLE ' + @UniqueTempTable)
END

ALTER PROCEDURE [dbo].[SearchTables_TEST] 
    @SearchStr NVARCHAR(60) ,
    @GenerateSQLOnly Bit = 0 ,
    @SchemaNames VARCHAR(500) ='%' 
AS 
SET NOCOUNT ON 

DECLARE @MatchFound BIT 
SELECT @MatchFound = 0 

DECLARE @CheckTableNames Table ( 
    Schemaname sysname ,
    Tablename sysname 
) 

DECLARE @SearchStringTbl TABLE ( 
    SearchString VARCHAR(500) 
) 

DECLARE @SQLTbl TABLE ( 
    Tablename SYSNAME ,
    WHEREClause VARCHAR(MAX) ,
    SQLStatement VARCHAR(MAX) ,
    Execstatus BIT 
) 

DECLARE @SQL VARCHAR(MAX) 
DECLARE @TableParamSQL VARCHAR(MAX) 
DECLARE @SchemaParamSQL VARCHAR(MAX) 
DECLARE @TblSQL VARCHAR(MAX) 
DECLARE @tmpTblname sysname 
DECLARE @ErrMsg VARCHAR(100) 
DECLARE @RowCount INT -- 新增变量存储行数

IF LTRIM(RTRIM(@SchemaNames)) ='' 
BEGIN 
    SELECT @SchemaNames = '%' 
END 

IF CHARINDEX(',',@SchemaNames) > 0 
    SELECT @SchemaParamSQL = 'SELECT ''' + REPLACE(@SchemaNames,',','''as SchemaName UNION SELECT ''') + '''' 
ELSE 
    SELECT @SchemaParamSQL = 'SELECT ''' + @SchemaNames + ''' as SchemaName ' 

SELECT @TblSQL = 'SELECT SCh.NAME,T.NAME FROM SYS.TABLES T JOIN SYS.SCHEMAS SCh ON SCh.SCHEMA_ID = T.SCHEMA_ID INNER JOIN [DynaForms].[dbo].[Enums_Tables] et on (et.Id = T.NAME COLLATE Latin1_General_CI_AS) ' 

INSERT INTO @CheckTableNames (Schemaname,Tablename) 
EXEC(@TblSQL) 

IF NOT EXISTS(SELECT 1 FROM @CheckTableNames) 
BEGIN 
    SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter' 
    PRINT @ErrMsg 
    RETURN 
END 

IF LTRIM(RTRIM(@SearchStr)) ='' 
BEGIN 
    SELECT @ErrMsg = 'Please specify the search string in @SearchStr Parameter' 
    PRINT @ErrMsg 
    RETURN 
END 
ELSE 
BEGIN 
    SELECT @SearchStr = REPLACE(@SearchStr,',,,',',#DOUBLECOMMA#') 
    SELECT @SearchStr = REPLACE(@SearchStr,',,','#DOUBLECOMMA#') 
    SELECT @SearchStr = REPLACE(@SearchStr,'''','''''') 
    SELECT @SQL = 'SELECT ''' + REPLACE(@SearchStr,',','''as SearchString UNION SELECT ''') + '''' 
    INSERT INTO @SearchStringTbl (SearchString) 
    EXEC(@SQL) 
    UPDATE @SearchStringTbl SET SearchString = REPLACE(SearchString ,'#DOUBLECOMMA#',',') 
END 

INSERT INTO @SQLTbl(Tablename,WHEREClause) 
SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME), 
( 
    SELECT '[' + SC.Name + ']' + ' LIKE ''' + REPLACE(SearchSTR.SearchString,'''','''''') + ''' OR ' + CHAR(10) 
    FROM SYS.columns SC 
    JOIN SYS.types STy ON STy.system_type_id = SC.system_type_id AND STy.user_type_id =SC.user_type_id 
    CROSS JOIN @SearchStringTbl SearchSTR 
    WHERE STY.name in ('varchar','char','nvarchar','nchar','text') 
        AND SC.object_id = ST.object_id 
    ORDER BY SC.name 
    FOR XML PATH('') 
) 
FROM SYS.tables ST 
JOIN @CheckTableNames chktbls ON chktbls.Tablename = ST.name 
JOIN SYS.schemas SCh ON ST.schema_id = SCh.schema_id AND Sch.name = chktbls.Schemaname 
GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME); 

-- 修改：生成统计行数的SQL
UPDATE @SQLTbl 
SET SQLStatement = 'SELECT COUNT(1) FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) 

DELETE FROM @SQLTbl WHERE WHEREClause IS NULL 

DECLARE @output TABLE (Id VARCHAR(50), Name VARCHAR(100)) 

WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
BEGIN 
    SELECT TOP 1 @tmpTblname = Tablename , @SQL = SQLStatement 
    FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0 

    IF @GenerateSQLOnly = 0 
    BEGIN 
        -- 修改：用sp_executesql获取行数
        EXEC sp_executesql @SQL, N'@RowCount INT OUTPUT', @RowCount OUTPUT
        IF @RowCount > 0 
        BEGIN 
            SELECT @MatchFound = 1 
            INSERT INTO @output (Id, Name) 
            Select * from [DynaForms].[dbo].[Enums_Tables] 
            where id = parsename(@tmpTblname,1)
        END 
    END 
    ELSE 
    BEGIN 
        PRINT REPLICATE('-',100) 
        PRINT @tmpTblname 
        PRINT REPLICATE('-',100) 
        PRINT @SQL
    END 

    UPDATE @SQLTbl SET Execstatus = 1 WHERE Tablename = @tmpTblname 
END 

SELECT * FROM @output 

IF @MatchFound = 0 
BEGIN 
    SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter' 
    PRINT @ErrMsg 
    RETURN 
END 

SET NOCOUNT OFF

内容的提问来源于stack exchange，提问作者Aires Menezes