Steve Jones from voiceofthedba.com, editor on SQLServerCentral.com gave me an opportunity to publish humble article about workaround solution for using temp table in user-defined function. I was very pleased and I am now proud that my article is on SQLServerCentral homepage (at least for today).

After reading discussion under article today, I realized that I should also mention practical scenario which leads me to this nasty workaround. Here it is:

I needed to share data among stored procedures because I had very complex task which required to pull data from fixed tables to some temporary structures and do some logic above them. I picked #table solution for sharing data because all others (output parameters, UDDT, …) were not sufficient for my scenario. Then I’ve started to “encapsulate” this complex logic and realized that you cannot use #table with function. I made up synonym workaround at this point. At the end of a day I had to use process-keyed tables for data sharing because this workaround had very big maintenance drawback as described in article.

Article is here.