创建含临时表的SQL函数报错“无法从函数内访问临时表”如何解决

SQL Server 自定义函数有明确的限制规则，不允许在函数内部访问、创建本地临时表（以#为前缀的临时表），该限制是为了保证函数的确定性，避免函数修改会话级别的状态资源，所以你会遇到Cannot access temporary tables from within a function的报错。

你的场景下直接用表变量替换临时表即可，表变量属于函数内部的局部资源，符合函数的使用规则。同时你原代码还存在几处语法错误，修正后的完整函数代码如下：

CREATE FUNCTION FunctionTest (
    @Anio   int=null,
    @Mes    int=Null,
    @Meses  int=6
)
RETURNS @Tabla TABLE (
    AnioMes INT,
    Viaje VARCHAR(30),
    IdPorte INT,
    Carga VARCHAR(20),
    Peso numeric(32, 16)
)
AS
BEGIN
    Declare @AnioMes    varchar(8),
            @AnioMes6   varchar(8)
    -- 声明表变量替代临时表#Temp
    DECLARE @Temp TABLE (
        AnioMes INT,
        Viaje VARCHAR(30),
        IdPorte INT,
        Carga VARCHAR(20),
        Peso numeric(32, 16)
    )

    if @Anio is null
        Select  @Anio   = YEAR(GETDATE()),
                @Mes    = MONTH(GETDATE())

    Select  @AnioMes    = (case when @Mes=12 then @Anio+1 else @Anio end *100 + Case when @Mes=12 then 1 else @Mes+1 end)*100 + 1
    Select  @AnioMes6   = convert(varchar(8), DATEADD(mm, -@Meses, @AnioMes), 112 )

    -- 修正原语法冲突：去掉冗余的INSERT INTO @Tabla，直接插入到表变量@Temp
    INSERT INTO @Temp (AnioMes,Viaje,IdPorte,Carga,Peso)   
    SELECT  year(cpsj.Delivery)*100 + MONTH(cpsj.Delivery) as AnioMes,  
            tr.TId as Viaje,
            cpsj.PId as IdPorte,            
            CASE WHEN tr.Load = 1 THEN 'CARGADO'
                WHEN tr.Load = 2 THEN 'VACIO'
            END  as Carga, 
            cpsj.Weight as Peso
    FROM BDNEW.dbo.CENPACKSTOREJOIN cpsj 
    inner join TRANS tr on cpsj.ipId = tr.ipId
    inner join OPERA oper on tr.OId = oper.OId  
    WHERE   cpsj.Id = 'ID001'
    AND     tr.Area = 'lost'
    AND     tr.Status       = 2
    GROUP BY cpsj.Delivery, cpsj.IName, tr.TId, cpsj.PId, tr.Load, cpsj.Weight
    ORDER BY cpsj.ipId

    if @AnioMes6 < '20160101'
        insert into @Temp (AnioMes,Viaje,IdPorte,Carga,Peso)
        SELECT  Year(cpsj.Delivery)*100 + MONTH(cpsj.Delivery) as AnioMes,  
                tr.TId as Viaje,
                cpsj.PId as IdPorte,        
                CASE WHEN tr.Load = 1 THEN 'CARGADO'
                    WHEN tr.Load = 2 THEN 'VACIO'
                END  as Carga, 
                cpsj.Weight as Peso
        FROM BDOLD.dbo.CENPACKSTOREJOIN cpsj 
        inner join TRANS tr on cpsj.ipId = tr.ipId
        inner join OPERA oper on tr.OId = oper.OId
        WHERE   cpsj.Id = 'ID001'
        AND     tr.Area = 'lost'    
        AND     tr.Status       = 2
        GROUP BY cpsj.Delivery, cpsj.IName, tr.TId, cpsj.PId, tr.Load, cpsj.Weight
        ORDER BY cpsj.ipId

    Delete  @Temp
    where   viaje in (
                    select MAX(Viaje)
                    from    @Temp
                    group by IdPorte
                    having COUNT(IdPorte) > 1
                    )

    -- 把最终处理结果插入到返回的表变量中，修正原GROUP BY语法错误的逗号
    INSERT INTO @Tabla (AnioMes,Viaje,IdPorte,Carga,Peso)
    Select  AnioMes,
            Viaje,
            IdPorte, 
            Carga, 
            Peso
    from @Temp
    GROUP BY AnioMes,IdPorte, Viaje, Carga, Peso
    ORDER BY AnioMes,IdPorte

RETURN 
END

如果你后续的逻辑需要用到临时表的特性（比如大数量下的索引优化、统计信息支持），也可以把该逻辑改为存储过程实现，存储过程没有临时表使用的限制。

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