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