无法从函数中访问临时表

Question

我想获得特定记录的计数。因此，我的查询将如下所示。。。

SELECT
    ID, 
    NAME,
    (SELECT...) AS UserCount // Stmt1
FROM MyTable

问题是，"Stmt1"是一个复杂的语句，不能写成innerquery。好吧，我可以使用函数，但语句中包含"CREATE TABLE"，所以我得到以下错误消息

Cannot access temporary tables from within a function.

完成任务的最佳方式是什么？

Answer 1

您可以使用用户定义的表类型来解决您的问题。

您只需创建一个类似的表变量

CREATE TYPE [dbo].[yourTypeName] AS TABLE(
    [columeName1] [int] NULL,
    [columeName2] [varchar](500) NULL,
    [columeName3] [varchar](1000) NULL
)
GO

你可以在你的函数中声明这个表变量，比如

    CREATE FUNCTION [dbo].[yourFunctionName] 
( 
    @fnVariable1 INT ,
    @yourTypeNameVariable yourTypeName READONLY
) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 
    SELECT .................
        FROM @yourTypeNameVariable 
        WHERE ........
    RETURN @r 
END 

在您的程序中，您可以像一样声明您的表类型

DECLARE @yourTypeNamevaribale AS yourTypeName 

你可以像一样在这个表中插入值

insert into @yourTypeNamevaribale (col,col,..)values(val,val,..)

像一样将其传递给您的函数

dbo.yourFunctionName(fnVariable1 ,@yourTypeNamevaribale )

请采用这种方法，谢谢

Answer 2

是的，您不能使用#temp表。

既然您使用的是SQL Server 2008，为什么不使用表变量而不是#temp表呢？试试看。

Answer 3

我看到这篇文章时，我开始使用表变量，出于性能原因切换到临时表，结果发现临时表不能在函数中使用。

我会犹豫是否使用表变量，尤其是如果你正在处理大型结果集，因为这些结果集保存在内存中。查看此帖子。。。

http://totogamboa.com/2010/12/03/speed-matters-subquery-vs-table-variable-vs-temporary-table/

其他选择是。

1. 将临时表结果提取到另一个表函数中
2. 将代码转换为使用子查询

Answer 4

在99,99%的情况下，不需要任何临时表或子查询技巧，而是将COUNT、SUM或AVG等聚合函数与OVER子句和（通常）PARTITION BY结合使用。

我不确定OP试图实现什么，但我认为UserCount在某种程度上与MyTable中的值有关。因此，必须有一种方法将MyTable连接到生成UserCount的任何表。

最简单的例子是显示所有用户和用户总数

SELECT id
    , name
    , user_count = COUNT(*) OVER()
FROM MyUsers