Using table Variables Create outside a UDF inside a UDF

[Vimalsoft Pty Ltd]

Good Day All i have the Following sp

/****** Object: StoredProcedure [dbo].[sp_Timetable_View] Script Date: 01/15/2010 10:51:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Timetable_View] @selectionType varchar(30), -- either Venues, Staff, Subjects, Curricula etc...
@selectedItems ntext, @selectedTerms ntext

AS

/*This Part of the code was Representing the Sp Get_Staff_Cycles_For_TimeTable
due to temp table scope , i had to put the code here so that the temp tables will be available

/*This code of the code will Add the Cycles to be Displayed ina string
*/
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[#temp]'))
drop table [#temp]
--Creation of Temp1
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE,MTN.ACTV AS [ACTV]
into #temp
FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID

--STEP 3 HERE WE ARE CREATING A TEMP TABLE
--CHECK IF THE TABLE EXISTS FIRST IF IT DOES DROP IT
--HERE WE ARE CREATING A FIELD CYCLEIDlIST THAT IS EMPTY THAT WILL BE POPULATED LATER
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[#TempSummary]'))
drop table [#TempSummary]

SELECT DESCR, dbo.GetSortedString(Cast(NULL AS varchar(8000))) AS CycleIdList,ACTV
INTO #TempSummary
FROM #temp (NOLOCK)
GROUP BY DESCR,ACTV
ORDER BY DESCR,ACTV

--CHECK THE TABLE CONTENTETS
--SELECT * FROM TempSummary
--WE ONLY SAW NAMES THAT ARE NOT DUPLICATED AND NULLS IN THE OTHER FIELD

--HERE WE UPDATE THE TEMPSUMMARY TABLE AND DO SOME CONCATINATING AND THIS IS VERY FAST,
--STEP 4
UPDATE #TempSummary
SET CycleIdList = dbo.Concat(#TempSummary.Descr,actv)

--SELECT * FROM TempSummary
--LETS CHECK THE TEMP SUMMARY TABLE
--select * from TempSummary

--IT CONTAINS ALL THE DATA THAT WE WANT :)
--Select DISTINCT Descr AS [Staff],dbo.DistinctList(Cycles,Descr) As [Cycles]
--into Temp2 From temp

/*============================================================================================================

Nornmal sp_Timetable_View sp was Starting here

*/

Declare @xmldoc int
EXEC sp_xml_preparedocument @xmldoc OUTPUT, @selectedItems --Create an internal representation of the XML document

-- turn the xml into a table with characteristics of the given

[Mycroft Holmes]

You can use global temp tables ##TableName, this may work. Caveat, I have never attempted this so I'm not sure. You need to be careful with these, if you have multiple users processing then you will get a race condition. You can also use XML variables to pass table data between a proc and a function, I'm not sure how you would return the XML to the proc but it could always be your output! You should be able to return an XML variable as well.

Never underestimate the power of human stupidity RAH

[Vimalsoft Pty Ltd]

I tried the Global temps but the UDF does not want to associate itself with anything thathas to do with # and i tried table Variables and the Query perfomance degraded. and again i tried Common Table Express and the same thing, the Perfomance

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Lost User]

Vuyiswa Maseko wrote:

SELECT @s = @s + IsNull(',' + Cast(Cycletemplate AS varchar), '') FROM #Temp (NOLOCK)

The #Temp table would be empty there? How about using a real table, instead of a temporary one?

I are Troll :suss: