Click here to monitor SSC
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
SQLTreeo
SQLTreeo
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 94
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
Carlo Romagnano
Carlo Romagnano
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

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

I run on tuttopodismo
Phil Factor
Phil Factor
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 2968
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1839 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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1048
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 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
SQLTreeo
SQLTreeo
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 94
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
alen teplitsky
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2354 Visits: 4661
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1475 Visits: 13625
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