Cannot access temporary tables from within a function

Question

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 ?

score 34 · Accepted Answer · answered Mar 05 '12 at 05:07

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

score 4 · Answer 2 · answered Mar 05 '12 at 04:37

4

Yes you can not use #temp table.

As you are using SQL Server 2008, why don't you use table variable instead of #temp tables? Give it a try.

answered Mar 05 '12 at 04:37

Rajan

198
1
2
10

1

Agreed use the Declare @TempTable as TABLE ( elementid int , element VARCHAR(8000) ) instead of #table – Johann Combrink Jul 12 '16 at 16:59

score 1 · Answer 3 · edited Oct 19 '20 at 03:11

1

I came across this post as I started using table variables and switched to temporary tables for performance reasons only to find temporary tables couldn't be used in a function.

I would be hesitant about using table variables especially if you are playing with large result sets, as these are held in memory. See this post...

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

Other alternatives would be..

Extracting the temporary table result into another table function.
Converting the code into using sub-queries

edited Oct 19 '20 at 03:11

Hobo

7,536
5
40
50

answered Apr 19 '13 at 00:36

Mick

6,527
4
52
67

table variables are not stored "only in memory": http://sqlserverplanet.com/tsql/yet-another-temp-tables-vs-table-variables-article – steve_ash Apr 14 '14 at 15:24

score 0 · Answer 4 · answered Dec 21 '15 at 13:14

In 99,99% of cases there is no need for any tricks with temp tables or subqueries, but use aggregation functions like COUNT, SUM or AVG in combination with OVER clause and (often) PARTITION BY.

I am not sure what the OP tried to achieve but I assume that the UserCount is somehow related to the values in MyTable. So there must be a way to join MyTable to whatever table that produces UserCount.

The most simple example is to show all users and the total number of users

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

Cannot access temporary tables from within a function

4 Answers4

Linked