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 ?

Answer 1

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 )