SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to use temporary table in function


How to use temporary table in function

Author
Message
Pim van Ekelenburg
Pim van Ekelenburg
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 97
Comments posted to this topic are about the item How to use temporary table in function

__________________________________________________________________________________________SQLTreeo.com [size=1]- My Blog and Free SSMS Productivity Add-In[/size]
Carlo Romagnano
Carlo Romagnano
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10970 Visits: 3476
I like dirty "solutions". In extreme cases, I know that a way exists. :-D
Phil Factor
Phil Factor
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8254 Visits: 3073
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
Simple Talk
honza.mf
honza.mf
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3325 Visits: 1323
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
RichB
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5098 Visits: 1085
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
Andre Guerreiro
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3703 Visits: 1515
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. Smile

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Pim van Ekelenburg
Pim van Ekelenburg
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 97
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 [size=1]- My Blog and Free SSMS Productivity Add-In[/size]
alen teplitsky
alen teplitsky
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13793 Visits: 4684
if you need to return a table, why not just use a view?
Greg Webb
Greg Webb
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 69

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
rfr.ferrari
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3311 Visits: 13646
very nice!!!


rfr.ferrari
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search