How to use temporary table in function

Question

Post reply

How to use temporary table in function

SQLTreeo

SSC Enthusiast

Points: 177
More actions
July 20, 2011 at 10:23 pm

#240194

Comments posted to this topic are about the item How to use temporary table in function
__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 1

I like dirty "solutions". In extreme cases, I know that a way exists. 😀

Phil Factor SSC-Insane Points: 20244 More actions · Answer 2

An interesting technique for sidestepping checks to see whether a function is deterministic. I'd love to see a practical example where this would be a good solution.

Best wishes,
Phil Factor

honza.mf SSCertifiable Points: 5519 More actions · Answer 3

Nice one. I hope I remember it the moment I (or someone else) will need it.

See, understand, learn, try, use efficient
© Dr.Plch

RichB SSCrazy Eights Points: 9651 More actions · Answer 4

Presumably that also has the effect of turning the temporary table into a global temporary table that magically disappears as soon as the original session clears it out? :hehe:

Bet that could make for some troublesome bughunts.... and some extremely awkward concurrency issues!

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 5

I'd like to see an example on when to use that.

But the thing is, a function is allowed to output a non-deterministic result. It just doesn't support temporary tables which is a different matter. And it doesn't allow side-effects.

Try this:

CREATE FUNCTION dbo.FN_WhichDateIsToday()

RETURNS DATETIME

AS

BEGIN

RETURN(SELECT GETDATE());

END;

Thank you for the small hint. 🙂

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

SQLTreeo SSC Enthusiast Points: 177 More actions · Answer 6

Practical example behind this article was following:

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.

Jakub Dvorak

__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 7

if you need to return a table, why not just use a view?

Greg Webb SSC Rookie Points: 27 More actions · Answer 8

CREATE FUNCTION dbo.FN_WhichDateIsToday()
RETURNS DATETIME
AS
BEGIN
RETURN(SELECT GETDATE());
END;

That's interesting - I'd always previously used a view which presented GetDate when I needed that. Hadn't occurred to me to check if the restriction was still in place 🙂

I confess I'm still a little confused why this trick is necessary rather than just using a table variable inside the function, which is permitted?

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 9

very nice!!!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

jbnv SSC Eights! Points: 973 More actions · Answer 10

I confess I'm still a little confused why this trick is necessary rather than just using a table variable inside the function, which is permitted?

Mr. Dvorak stated that he is sharing data among multiple stored procedures. I say that if you need to share data among multiple objects, you should put it in a named permanent table.

When I saw this article in the newsletter, my guess was that he'd tell us to use a CTE instead of a temporary table.

Jay Bienvenu | http://bienv.com | http://twitter.com/jbnv

laurah SSC Journeyman Points: 99 More actions · Answer 11

Why not just use: ???

CREATE FUNCTION funcName1 ()

RETURNS @tblResult TABLE

(Num1 INT,

Num2 INT,

Num3 INT)

AS BEGIN

INSERT @tblResult

(Num1, Num2, Num3)

SELECT 100, 200, 300

RETURN

END

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

RichB (7/21/2011)
Presumably that also has the effect of turning the temporary table into a global temporary table that magically disappears as soon as the original session clears it out? :hehe:
Bet that could make for some troublesome bughunts.... and some extremely awkward concurrency issues!

No... it wouldn't turn the temp table into a global one anymore than using a call to a temp table from a stored procedure that didn't actually build the temp table, so no concurrency issues.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

Jakub Dvorak (7/20/2011)
Comments posted to this topic are about the item <A HREF="/articles/UDF/74231/">How to use temporary table in function</A>

+10 for thinking outside of the box, Jakub. There have been a couple of times (and, no, I can't think of what they are, just now) where I'd have preferred to use a Temp Table instead of a Table Variable in a function. I've known this could be done with stored procedures (without the use of a Synonym) and, for some reason, never thought about doing it in a function by using a Synonym like you did. Well done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

jbnv (7/21/2011)
I confess I'm still a little confused why this trick is necessary rather than just using a table variable inside the function, which is permitted?
Mr. Dvorak stated that he is sharing data among multiple stored procedures. I say that if you need to share data among multiple objects, you should put it in a named permanent table.

That would either destroy the ability for concurrent runs (as would the use of a global temp table) or require the use of dynamic SQL for differently named permanent (or global temp) tables to all concurrency.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)