## Temp table in function

 Author Message Zahid Farooq SSC-Enthusiastic Group: General Forum Members Points: 140 Visits: 34 Can i use a temp table in function ? Mahesh Bote Ten Centuries Group: General Forum Members Points: 1104 Visits: 1274 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 GSquared SSChampion Group: General Forum Members Points: 14375 Visits: 9729 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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon pduplessis-723389 Ten Centuries Group: General Forum Members Points: 1263 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)ASBEGINInsert into @RESULT_SET Select YourFieldNamesfrom tablexwhere fieldy = @VariablesYouPassedRETURNEND tony.sawyer SSC Veteran Group: General Forum Members Points: 233 Visits: 146 You can't use the CREATE TABLE #TEMPTABLE command to create a table for the duration of the functionYou 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 milad.ahmadi2008 Forum Newbie Group: General Forum Members Points: 1 Visits: 0 thanks very much Evil Kraig F SSCertifiable Group: General Forum Members Points: 5697 Visits: 7660 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 FarrellNever 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 NetiquetteFor index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA