Is it possible to have temp tables in a function?

aarona picture aarona · Mar 23, 2012 · Viewed 53k times · Source

Apparently, I can't use them. I'm getting an error message like:

Invalid use of a side-effecting operator 'SELECT' within a function

If I want to do something like this:

select bleh
  into #temp
  from Blah

... inside a function.

Answer

Justin Pihony picture Justin Pihony · Mar 23, 2012

No, per this thread where the same question was asked, you cannot, but you can use a table variable

DECLARE @MyTempTableVariable TABLE (SCHEMA)

INSERT INTO @MyTempTableVariable
SELECT bleh
FROM bleh