incorporate the UDF functionality into the Sp

[vuyiswamb]

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

[samaru]

Maybe I'm not reading you correctly. Why can't you do this (take the logic from the concat UDF and put it above the UPDATE statement?:

DECLARE @s varchar(max)
DECLARE @Name varchar(50)
DECLARE @Actv int

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)

UPDATE #TempSummary
SET CycleIdList = @S

[vuyiswamb]

Thank you this is Perfect :)