Using table Variables Create outside a UDF inside a UDF

[vuyiswamb]

Good Day All

i have the Following sp

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
[B] SET CycleIdList = dbo.Concat(#TempSummary.Descr,actv)[/B]
--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 table 
SELECT * 
INTO #selectedItems
FROM OPENXML ( @xmldoc , '/Root/Tags' , 2) 
WITH 	(
		[TagID] int
	)

EXEC sp_xml_removedocument @xmldoc

create table #selectedActvs (
	id int
)

-- From the list of selected items of whichever type, find the list of activities these relate to; i.e. which activities are envolved in the selection
if (@SelectionType = 'Venues') begin
-- show the timetable for the selected activities
	Insert into #selectedActvs
		(id)
		select distinct Actv [Id] 
		from sol_actv_venu
		where venu in ( select TagID from #selectedItems )
end
if (@SelectionType = 'Staff') begin
	Insert into #selectedActvs
		(id)
		select distinct Actv [Id] 
		from sol_actv_staff
		where staff in ( select TagID from #selectedItems )		
end
if (@SelectionType = 'Subjects') begin
	Insert into #selectedActvs
		(id)
		select distinct Actv [Id] 
		from sol_actv_time
		inner join tbl_actv a on a.id = sol_actv_time.Actv
		inner join tbl_cntc ct on ct.id = a.cntcID
		where modlID in ( select TagID from #selectedItems )		
end
if (@SelectionType = 'SubjectContainer') begin
	Insert into #selectedActvs
		(id)
		select distinct Actv [Id] 
		from sol_actv_time
		inner join tbl_actv a on a.id = sol_actv_time.Actv
		inner join tbl_cntc ct on ct.id = a.cntcID
		inner join tbl_modl m on m.id=ct.modlid
		inner join mtm_modl_container mc on mc.modl=m.id
		where mc.container in ( select TagID from #selectedItems )		
end
if (@SelectionType = 'Curricula') begin
	Insert into #selectedActvs
		(id)
		select distinct sa.Actv [Id] 
		from sol_actv_time sa
		inner join mtm_subj_strm_actv ss on ss.actv = sa.actv
		inner join mtm_curr_strm cs on cs.subjstrmid = ss.strm
		inner join tbl_curr_strm c on c.id = cs.currstrmid
		where c.curr in ( select TagID from #selectedItems )		
end			
if (@SelectionType = 'Curriculum Streams') begin
	Insert into #selectedActvs
		(id)
		select distinct sa.Actv [Id] 
		from sol_actv_time sa
		inner join mtm_subj_strm_actv ss on ss.actv = sa.actv
		inner join mtm_curr_strm cs on cs.subjstrmid = ss.strm
		inner join tbl_curr_strm c on c.id = cs.currstrmid
		where c.id in ( select TagID from #selectedItems )		
end			

EXEC sp_xml_preparedocument @xmldoc OUTPUT, @selectedTerms		--Create an internal representation of the XML document

-- turn the xml into a table with characteristics of the given table 
SELECT * 
INTO #selectedTerms
FROM OPENXML ( @xmldoc , '/Root/Tags' , 2) 
WITH 	(
		[TagID] int
	)

EXEC sp_xml_removedocument @xmldoc

select distinct tt.Dy, tt.Sess, m.descr as  [Codes], m.LongName as[Description], ctyp.Abrev, ctyp.Descr as [Type], ct.Number,
a.GrpName, a.GrpNumber, a.Duration, 
CASE WHEN MAV.stud IS NULL THEN a.Students
ELSE MAV.STUD END as [Students], 
v.descr as [Venue], v.Capacity,
s.descr as [Staff], m.id as [ModlID], t.Descr as [Term]
, ISNULL(temp.CYCLEIDLIST,t.Descr) as [StaffTerm]
, dates.descr [Date],a.Length as [Length]
from sol_actv_time tt
inner join tbl_clmn dates on dates.id=tt.dy
inner join tbl_actv a on a.id = tt.actv
inner join #selectedActvs SelectedActvs on SelectedActvs.ID = a.id	-- filter the list of activies to be shown by the activities which fall in the selection
inner join tbl_cntc ct on ct.id = a.cntcid
inner join tbl_cntc_typ ctyp on ctyp.id = ct.cntctyp
inner join tbl_modl m on m.id = ct.modlid
inner join tbl_term t on t.id = a.term		--This will become t.id = actv.term when terms move to activities
inner join #selectedTerms selTerms on selTerms.TagID = a.term
left outer join sol_actv_venu ttVenu on ttVenu.actv = a.id
left outer join SOL_ACTV_VENU mav on mav.venu=ttVenu.Venu and mav.actv=a.id
left outer join tbl_venue v on v.id = ttVenu.Venu
left outer join sol_actv_staff ttStaff on ttStaff.Actv = a.id
left outer join mtm_actv_staff mas on mas.actv=ttStaff.actv and mas.staff=ttStaff.Staff
left outer join tbl_term staffTerm on staffTerm.id=mas.cycletemplate
left outer join tbl_staff s on s.id = ttStaff.Staff
left outer join [#TempSummary] temp on temp.descr = s.descr
order by tt.dy, tt.sess

This sp is fine and working Perfectly and i have a UDF defined like this

ALTER FUNCTION [dbo].[Concat] (@Name varchar(50),@Actv int)
RETURNS varchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
Declare @s varchar(max)
SET @s = ''
SELECT @s = @s + IsNull(',' + Cast(Cycletemplate AS varchar), '')
FROM #Temp (NOLOCK)
WHERE #Temp.Descr = @Name And Temp.Actv = @Actv
GROUP BY Cycletemplate
ORDER BY Cycletemplate
IF (@S IS NOT NULL) AND (@S <> '') SET @S = SubString(@s, 2, Len(@S)-1)
RETURN @S
END

now the UDF is used in the Bolded part of the Sp

i know i cant use a #temp table in a UDF , i want to access a temp table created in the Sp i want to use it in the UDF. i can use table Variables, but is there a Global table variable ? that i can use, that is created in the sp but used in the UDF. I need your Advice.

Thanks

[sknake]

One suggestion:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[#temp]'))

You can replace that with:

IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL DROP TABLE #Temp

Now on to your original question .. that is a lot of code. What are you trying to do exactly? And have you considered using permanent tables? You could create a "LoadMaster" table with an autoincrement column. When you start your sproc insert a value and retrieve the SCOPE_IDENTITY() . Then have child load tables from that with the proper table structure where you can insert your "LoadMasterId" then the other columns. Pass the "LoadMasterId" off to your processing logic so everything references permanent tables. It may also make debugging easier since you can go back and review the data.

[vuyiswamb]

Good Day sknake

Thanks for Pointing that out

IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL DROP TABLE #Temp

Before i used a Solid table , but now it becomes a Problem u in a multi user enviroment because some other users will receive the "temp table does not exist" or "temp table already exist" with #temp table it was going to be easy. but due to the fact that UDF does not like the #temp. Your Solution that you suggested will you please elaborate more deeply on it.

Thanks for you reply.

[sknake]

Its hard to give you an example since your queries are very complex but basically create a "Load" a set of Load tables matching your source table structure, with some additional columns.

Lets say I have a table called ExperimentReading that I want to do processing on in a `temporary` environment. Here is the original table:

CREATE TABLE [dbo].[ExperimentReading](
	[ExperimentReadingId] [int] IDENTITY(1000,1) NOT NULL PRIMARY KEY,
	[ExperimentId] [int] NULL,
	[ExperimentTubeId] [int] NULL,
	[ReadingTime] [int] NULL,
	[ReadingValue] [money] NULL
)

Now to set up the Load environment:

IF OBJECT_ID('LoadMaster', 'U') IS NOT NULL DROP TABLE LoadMaster
Create Table LoadMaster
(
  LoadMasterId int identity(1000, 1) PRIMARY KEY NONCLUSTERED,
  CreateDate DateTime,
  UserName varchar(20)
)
GO
IF OBJECT_ID('LoadExperimentReading', 'U') IS NOT NULL DROP TABLE LoadExperimentReading
Create Table LoadExperimentReading
(
    LoadExperimentReading int identity(1000, 1) PRIMARY KEY,
    LoadMasterId int NOT NULL,
  	--[ExperimentReadingId] [int] IDENTITY(1000,1) NOT NULL, Need to remove the identity attributes
  	ExperimentReadingId int NOT NULL,
	[ExperimentId] [int] NULL,
	[ExperimentTubeId] [int] NULL,
	[ReadingTime] [int] NULL,
	[ReadingValue] [money] NULL,
)
ALTER TABLE LoadExperimentReading ADD CONSTRAINT IX_LoadExperimentReading_Uniq_1 UNIQUE(LoadMasterId, ExperimentReadingId)

And how I would use these tables:

Declare @LoadMasterId int
INSERT INTO LoadMaster (CreateDate, UserName) Values (GetUtcDate(), 'Sknake')
Set @loadMasterId = CAST(SCOPE_IDENTITY() as int) --This is our load ID that we will reference

Insert Into LoadExperimentReading (LoadMasterId, ExperimentReadingId, [ExperimentId], [ExperimentTubeId], [ReadingTime], [ReadingValue])
Select 
@LoadMasterId, --Our load ID from the insert. Guarantees only the user who created it will access the data
CAST(ExperimentReadingId as int) As ExperimentReadingId, --Trims off the identity stuff from the source table
[ExperimentId], [ExperimentTubeId], [ReadingTime], [ReadingValue]
From [ExperimentReading]

It won't be a problem in a multi-user environment because when a user inserts a record to the LoadMaster table they are given a LoadMasterId. Insert all of the child/related records in to other load tables with the @ID pulled from the insert, and only reference those load records in your UDF/SP. You would change your existing logic to include a @LoadId int parameter.