我正在尝试对 udf 中的临时表运行选择查询。我找不到说明这是不允许的文档，但是当我将 tblDailyPricingAndVol 更改为 #dailyPricingAndVolBySymbol(当然是我的临时表)时，下面的存储过程不会编译。临时表是在更高级别创建的(在使用此函数的存储过程之前的存储过程中)，如果这会影响任何东西......提前致谢。

编辑: udf 只是作为调用它的存储过程的助手。我试图用它查询一个临时表，因为它每次运行时都会被调用数千次。它检索然后聚合的数据位于具有数百万行的表中。所以我将数据缩减为数百条记录，放入临时表中。这将显着加快函数的速度，尽管它仍然需要相当长的时间才能运行。

ALTER FUNCTION dbo.PricingVolDataAvailableToDateProvided
    (@Ticker nchar(10),
     @StartDate DATE,
     @NumberOfDaysBack int)
    RETURNS nchar(5)
AS
    BEGIN

    DECLARE @Result nchar(5)
    DECLARE @RecordCount int

    SET @RecordCount = (SELECT COUNT(TradeDate) AS Expr1
        FROM (SELECT TOP (100) PERCENT TradeDate
           FROM tblDailyPricingAndVol WHERE (Symbol = @Ticker) AND (TradeDate IN
                 (SELECT TOP (@NumberOfDaysBack) CAST(TradingDate AS DATE) AS Expr1
                  FROM tblTradingDays
                  WHERE (TradingDate <= @StartDate)
                  ORDER BY TradingDate DESC))
                  ORDER BY TradeDate DESC) AS TempTable)

    IF @RecordCount = @NumberOfDaysBack
        SET @Result = 'True'
    ELSE
        SET @Result = 'False'

    RETURN @Result

    END

最佳答案

正如其他发帖人所提到的，您不能在 UDF 中使用临时表。您可以做的是传递 User-Defined Table到你的功能。

User-Defined Table Types

In SQL Server 2008, a user-defined table type is a user-defined type that represents the definition of a table structure. You can use a user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that you want to use in a batch or in the body of a stored procedure or function.

更改代码的快速修复可能是

CREATE TYPE DailyPricingAndVolBySymbolType AS TABLE (<Columns>)
DECLARE @DailyPricingAndVolBySymbol DailyPricingAndVolBySymbolType

INSERT INTO @DailyPricingAndVolBySymbol SELECT * FROM #DailyPricingAndVolBySymbol

ALTER FUNCTION dbo.PricingVolDataAvailableToDateProvided (
  @DailyPricingAndVolBySymbol DailyPricingAndVolBySymbolType READONLY
  @Ticker nchar(10),
  @StartDate DATE,
  @NumberOfDaysBack int
) ...

关于sql-server - SQL Server 2008，我可以在 udf 中的 select 语句中引用临时表吗？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/9342595/