-
February 9th, 2010, 06:43 AM
#1
[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
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
-
February 9th, 2010, 11:12 AM
#2
Re: incorporate the UDF functionality into the Sp
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+...
-
February 9th, 2010, 11:14 AM
#3
Re: incorporate the UDF functionality into the Sp
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
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|