May 16, 2008 at 5:49 am
Can i use a temp table in function ?
May 16, 2008 at 6:00 am
[font="Verdana"]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[/font]
MH-09-AM-8694
May 16, 2008 at 11:25 am
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
May 21, 2008 at 3:58 am
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
May 22, 2008 at 3:51 am
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
April 12, 2011 at 1:30 am
thanks very much
April 12, 2011 at 1:42 am
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?
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply