Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Temp table in function Expand / Collapse
Author
Message
Posted Friday, May 16, 2008 5:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 1:45 AM
Points: 136, Visits: 34
Can i use a temp table in function ?
Post #501942
Posted Friday, May 16, 2008 6:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:57 AM
Points: 1,093, Visits: 1,222
Let us know what you are going to do? Coz 1st thing, object can't be create inside UDF.

by the way, the answer for your question is, NO

Mahesh


MH-09-AM-8694
Post #501953
Posted Friday, May 16, 2008 11:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
No temp tables in functions. However, you can use table variables. That might do what you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #502255
Posted Wednesday, May 21, 2008 3:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
You could create a table valued function, which stores the values in a resultset.
Temp tables wont be stored in here, but its almost the same...

Sample Code:

CREATE FUNCTION YourFunctionName (@VariablesYouNeedHere )
RETURNS @RESULT_SET TABLE (YourFieldNamesAndTypesHere)
AS

BEGIN
Insert into @RESULT_SET
Select YourFieldNames
from tablex
where fieldy = @VariablesYouPassed
RETURN
END
Post #504318
Posted Thursday, May 22, 2008 3:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 23, 2010 8:30 AM
Points: 229, Visits: 146
You can't use the CREATE TABLE #TEMPTABLE command to create a table for the duration of the function

You can create a table for the duration of the function by declaring it as a variable. We use the following code within a function to determine what date the last occurrence of a certain Cycle was.

declare @TempCycles table ( CycleID CHAR(3), ID INT)
INSERT INTO @TempCycles VALUES ('EOD',1)
INSERT INTO @TempCycles VALUES ('EOW',2)
INSERT INTO @TempCycles VALUES ('EOF',3)
INSERT INTO @TempCycles VALUES ('EOM',4)
INSERT INTO @TempCycles VALUES ('EOQ',5)
INSERT INTO @TempCycles VALUES ('EOH',6)
INSERT INTO @TempCycles VALUES ('EOY',7)

...
JOIN @TempCycles TC
ON TC.CycleID = BD.SYSTEM_CYCLE_ID
...
WHERE
...
AND TC.ID >= @ID
...

Hope this helps

Tony
Post #505057
Posted Tuesday, April 12, 2011 1:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 12, 2011 1:28 AM
Points: 1, Visits: 0
thanks very much
Post #1091902
Posted Tuesday, April 12, 2011 1:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:15 PM
Points: 5,383, Visits: 7,454
I realize this is 2 years old but since it got necro'd anyway, what is this:

INSERT INTO @TempCycles VALUES ('EOF',3)

End Of Fortnight?



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1091906
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse