Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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:

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
Total article views: 8639 | Views in the last 30 days: 13
 
Related Articles
FORUM

create function

create function

FORUM

Synonym-Given String

Sql Server Query -help(Synonym for Given String)

FORUM

Creating a SQL 2008 synonym to named instance

SQL 2008 synonym

FORUM

Synonym with servername\instancename

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

ARTICLE

Practical Uses for Synonyms in SQL Server

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

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones