Help with the Following SQl SP

[vuyiswam]

Good day All

I have a Challenge. I have the Following StoredProcedure that is doing the Following

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

--Creation of Temp1
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE
into temp FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN tbl_Cycles_Staff C
ON C.IDL = MTN.ID

All this takes less than a second with (17672 row(s) affected)

and its Cool and it Bring records like this

ID              DESCR           CYCLETEMPLATE
===============================
7620	     Campbell P Dr	26
7620	     Campbell P Dr	27
7620	     Campbell P Dr	28
7620	     Campbell P Dr	29
7620        Campbell P Dr	      31
7621	      Jones D Dr	        23
7621	      Jones D Dr	        24
7621	      Jones D Dr	        26
7621	      Jones D Dr	        28
7621	      Jones D Dr	        29
7621	      Jones D Dr	        33
7621	      Jones D Dr 	       34

This is Cool, So now i want to Have one Campbell P Dr wilth all the CycleTemplate Feld on one line and not Duplicated and sepated by a "," So in Simple it Should be like this

ID              DESCR           CYCLETEMPLATE
===============================
7620	Campbell P Dr	26,2728,29,31
7621	Jones D Dr	23,24,26,28,29,33,34

So to do this i created a user defined function to Remove the Duplicates in a Table Level, the Function looks like this

/*This Userdefined Function is used to Remove Duplicates*/

ALTER FUNCTION [dbo].[DistinctList]
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX)
DECLARE @Pos INT
DECLARE @rList VARCHAR(MAX)

SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList 
VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT @rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END

And the above function remove the first above mentioned problem and place the cycletemplate like this. Now

ID              DESCR           CYCLETEMPLATE
===============================
7620	Campbell P Dr	26,27,28,29,31,26,26,,28,28
7621	Jones D Dr  	23,24,26,28,29,33,34,34,34,34,34

Now as you can see the Duplicates on the Row level are removed but not the Field level are Still there. So i created the Following User Defined Function that Removes the Duplicates in a Field Level like this

ALTER FUNCTION [dbo].[GetCycle_Timetable] (@Descr Varchar(50))  
RETURNS Varchar(500)
AS  
BEGIN
Declare @RetStr as varchar(500)
DECLARE @Cycle Int  --<-- Assuming Cycle field  is of Type Integer
--Creating a Cursor-- 
Declare TmpCur Cursor For 
select CyCleTEMPLATE From temp Where Descr = @Descr
Open TmpCur  --open the cursor
Set @RetStr='' --initialize the string to nothing
Fetch Next 
From TmpCur Into @Cycle --take the cycles into the cursor variable 
While @@Fetch_status=0
Begin
	Set @RetStr = @RetStr +
	 Case when @RetStr=''
   then
		 '' else
   ' ' End 
  + Cast(@Cycle as varchar)
	Fetch Next From TmpCur Into @Cycle
End
Close TmpCur 
Deallocate TmpCur 
return (@RetStr)
END

and my sp i conbine this and Call it like this

Select DISTINCT Descr AS [Staff],dbo.DistinctList(.dbo.[GetCycle_Timetable](Descr),'') As [Cycles] 
into Temp2 From temp

and it worked Perfectly and brought desired Results as i shown in the Beginning. Now My Problem with this it Runs for 3 Minutes and in an ASP.net page it times out.

Is there another way that i could have dont this ?

Please Help me with your Example Code by Changing the statement in your way.


Thank you