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],[B]dbo.DistinctList[/B](.dbo.[[B]GetCycle_Timetable[/B]](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.