[RESOLVED] incorporate the UDF functionality into the Sp

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

Code:

---

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

Code:

---

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

Code:

---

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

---

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

Code:

---

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

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+...

THANKS man :)

i found a solution

Code:

---

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

---