无法从函数中访问临时表

Question

我想要获取特定记录的数量。因此，我的查询将如下所示...

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

问题在于'Stmt1'是一个复杂的语句，无法写成内部查询。 虽然我可以使用函数，但该语句包括'CREATE TABLE'，所以我会收到以下错误消息：

“无法从函数中访问临时表。” 最佳的完成任务的方法是什么？

- Rauf

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 )

请使用这种方法，谢谢

- ellickakudy rajeesh

Answer 2

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

既然你正在使用 SQL Server 2008，为什么不使用表变量代替 #temp 表呢？ 试一试吧。

- Rajan

---

同意使用Declare @TempTable as TABLE ( elementid int , element VARCHAR(8000) ) 而不是 #table。 - Johann Combrink

Answer 3

我因性能原因开始使用表变量并切换到临时表，但发现临时表无法在函数中使用，所以看到了这篇文章。

如果你打算处理大的结果集，我会对使用表变量持怀疑态度，因为它们会保存在内存中。可以参考这篇文章...

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

其他的替代方案包括：

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

- Mick

---

表变量并非“仅存储在内存中”：http://sqlserverplanet.com/tsql/yet-another-temp-tables-vs-table-variables-article - steve_ash

Answer 4

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

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

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

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

- Konstantin