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 ) 

please go for this method, thank you

Answer 2

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.