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
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7532 Visits: 3397
I like dirty "solutions". In extreme cases, I know that a way exists. :-D

I run on tuttopodismo
Phil Factor
Phil Factor
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4880 Visits: 3031
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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2555 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
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3022 Visits: 1065
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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2279 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
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7136 Visits: 4674
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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2167 Visits: 13639
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