[RESOLVED] incorporate the UDF functionality into the Sp

[vuyiswam]

Good Day All

I have an Sp and UDF. the UDF cant take #tables. so i want to incorporate the functionality that is being provided by the udf and make it part of the code. the first part of my statement creates a solid table that is being used in the UDF


truncate table temp
INSERT INTO temp
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE,MTN.ACTV AS [ACTV]
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
END
ELSE
BEGIN
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
END

and later in my Sp i have this line of statement


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


which has no problem and later i want to update the Filed in the #TempSummary table like this


UPDATE #TempSummary
SET CycleIdList = dbo.Concat(#TempSummary.Descr,#TempSummary.actv)

now the problem is here, the Concat is the UDF. defined like this

create 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 i want to incorporate the functionality of the UDF and get rid of it. The UDF use the Temp created in the First example code based on the two fields. and generate the string and update the table #TempSummary. How can i incorporate this functionality without using UDF.

Thanks

[nelo]

Hi,

My SQL is not as strong as my C# :) but I would recommend looking into what you can do with the 'CROSS APPLY'. I assume you're running SQL Server 2005+...

[vuyiswam]

THANKS man :)

i found a solution

SELECT DESCR,ACTV,[CYCLETEMPLATE] = STUFF((SELECT ',' + CAST(CYCLETEMPLATE AS VARCHAR(MAX))
FROM #temp
WHERE DESCR = t.DESCR
AND ACTV = t.ACTV
FOR XML PATH('')),1,1,'')
into #TempSummary2 FROM #temp t
GROUP BY DESCR,ACTV