无法从函数内部访问临时表 [英] Cannot access temporary tables from within a function

问题描述

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

  SELECT 
 ID，
 NAME，
（SELECT ...）AS UserCount // Stmt1 
 FROM MyTable 
  

问题是'Stmt1'是一个复杂的陈述，它不能被写成内部查询。
嗯，我可以使用函数，但声明包含'CREATE TABLE'，所以我得到以下错误消息：
$ b $ p 无法访问函数中的临时表。

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

解决方案

您可以使用用户定义的表类型来解决您的问题。一个表变量像

  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 
 
选择...... ........... 
 FROM @yourTypeNameVariable 
 WHERE ........ 
 RETURN @r 
 END 
  
 
 $ b 
在你的过程中，你可以声明你的表类型如
  DECLARE @yourTypeNamevaribale AS yourTypeName 
  
您可以将值插入到
  insert into @yourTypeNamevaribale（col，col，..）values（val，val，..）
  
传递给你的函数，如
  dbo.yourFunctionName（fnVariable1，@ yourTypeNamevaribale）
  
请去这个方法，谢谢
 
I would like to get count of specific records. So my query will look like the following...
SELECT
    ID, 
    NAME,
    (SELECT...) AS UserCount // Stmt1
FROM MyTable
The issue is that, 'Stmt1' is a complex statement and it cannot be written as innerquery. 
Well, I can use functions, but the statement includes 'CREATE TABLE' so I get the following error message

Cannot access temporary tables from within a function.

What is the best way to accomplish the task ?
解决方案
You can use user defined table type to solve your problem.

You just create a table variable like
CREATE TYPE [dbo].[yourTypeName] AS TABLE(
    [columeName1] [int] NULL,
    [columeName2] [varchar](500) NULL,
    [columeName3] [varchar](1000) NULL
)
GO
and you can declare this table variable in your function like
    CREATE FUNCTION [dbo].[yourFunctionName] 
( 
    @fnVariable1 INT ,
    @yourTypeNameVariable yourTypeName READONLY
) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 

    SELECT .................
        FROM @yourTypeNameVariable 
        WHERE ........
    RETURN @r 
END 
On your procedure you can declare your table type like
DECLARE @yourTypeNamevaribale AS yourTypeName 
And you can insert values to this table like 
insert into @yourTypeNamevaribale (col,col,..)values(val,val,..)
pass this to your function like
dbo.yourFunctionName(fnVariable1 ,@yourTypeNamevaribale )
please go for this method, thank you

                        
这篇关于无法从函数内部访问临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！