Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

How to use temporary table in function

By Jakub Dvorak,

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:

GO INSERT INTO #temp VALUES (1),(2),(3)
GO CREATE SYNONYM temp_table_synonym FOR #temp
GO CREATE FUNCTION fn_select_temp_table ()
SELECT * FROM temp_table_synonym
GO SELECT * FROM fn_select_temp_table()
Total article views: 8789 | Views in the last 30 days: 3
Related Articles

create function

create function


Synonym-Given String

Sql Server Query -help(Synonym for Given String)


Creating a SQL 2008 synonym to named instance

SQL 2008 synonym


Synonym with servername\instancename

I am trying to create a synonym as follows. . .


Practical Uses for Synonyms in SQL Server

The concept of a synonym was introduced in SQL Server 2005. Synonyms are very simple database objec...