Use a temp table in a Table-valued Functions

Question

I'm trying to use a temp table in a table-valued function, but it seems I can not. Is there any other way I can use a temp table in a table-valued function?

CURRENT CODE:

CREATE FUNCTION dbo.fnt_AllChildren (@ParentName VARCHAR(255))   RETURNS @return_variable TABLE      (         Id INT,         Name VARCHAR(255),         ParentId INT,         ParentName VARCHAR(255)     ) AS BEGIN     CREATE TABLE #Child (Id INT, Name VARCHAR(255), ParentId INT, ParentName VARCHAR(255))     CREATE TABLE #Parent (Id INT, Name VARCHAR(255), ParentId INT, ParentName VARCHAR(255))       INSERT #Child (Id, Name, ParentId, ParentName)     SELECT child.Id, child.Name, child.ParentId, parent.Name     FROM dbo.t_mytable child     INNER JOIN dbo.t_mytable parent ON child.ParentId = parent.Id     WHERE parent.Name = @ParentName      WHILE (@@ROWCOUNT > 0)     BEGIN         INSERT INTO @return_variable         SELECT * FROM #Child          DELETE FROM#Parent          INSERT INTO #Parent         SELECT * FROM #Child          DELETE FROM #Child                  INSERT INTO #Child (Id, Name, ParentId, ParentName)         SELECT child.Id, child.Name, child.ParentId, parent.Name         FROM dbo.t_mytable child         INNER JOIN #Parent parent ON child.ParentId = parent.Id     END     RETURN END GO 

Answer 1

You can use a table variable instead.

DECLARE @Child TABLE (Id INT, Name VARCHAR(255), ParentId INT, ParentName VARCHAR(255)) DECLARE @Parent TABLE (Id INT, Name VARCHAR(255), ParentId INT, ParentName VARCHAR(255)) 

Answer 2

No.

You can use @table_variables though. Although from a quick glance maybe a recursive CTE might work for you rather than using these child/parent tables at all.