How to use temporary table in function

,

There are many blog posts about how to use temp table in functions but I didn't find any saying that it IS possible. It is really possible but I must admit that solution is very dirty and should be used only in emergency.

You cannot use #table in function freely because functions (UDF) can contain only calls to deterministic "things" as could stay deterministic themselves. Select to temp table is not deterministic because table doesn't have to exists.

Furthermore, calls to non-deterministic functions are also prohibited because of same reason (there are some exceptions which you can find here).

There is some - dirty - workaround which uses SQL Server synonyms to substitute and point to temp table. My personal opinion is that it should be prohibited and it is maybe bug in SQL Server. It brings big issues in maintenance because all temp tables behind synonyms being used in functions must be created before function is created or altered.

To make long story short, I don't recommend it but it works. Try this code:

CREATE TABLE #temp (id INT)
GO INSERT INTO #temp VALUES (1),(2),(3)
GO CREATE SYNONYM temp_table_synonym FOR #temp
GO CREATE FUNCTION fn_select_temp_table ()
RETURNS TABLE
AS
RETURN
(
SELECT * FROM temp_table_synonym
)
GO SELECT * FROM fn_select_temp_table()
GO

Rate

2.82 (50)

Share

Share

Rate

2.82 (50)