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.IDAll 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 34This 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,34So 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
ENDAnd 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,34Now 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)
ENDand 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 tempand 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
