Improving speed and efficiency using T-SQL to concatenate data
I personally prefer to avoid cursor-based looping in T-SQL or PL/SQL because of the obvious performance gains. This blog post is for a friend who needed a quick way of looping through and building a string in T-SQL without using cursors.
|
– Function to concatenate records in a single variable using set-based processing SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
CREATE FUNCTION fnGenerateSkillXML (@intSkillGroupID numeric) RETURNS varchar(8000) AS BEGIN
DECLARE @strReturn VARCHAR(8000) SET @strReturn = ” SELECT @strReturn = @strReturn + ‘<SKILL><SKILL_ID>’ + convert(varchar, SKILL_ID) + ‘</SKILL_ID><SKILL_NAME>’ + SKILL_NAME + ‘</SKILL_NAME></SKILL>’ FROM VW_DISTINCT_SKILLS_BY_SKILL_GROUP WHERE SKILL_GROUP_ID = @intSkillGroupID ORDER BY SKILL_NAME
return @strReturn
END
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
|
|
–Application SELECT distinct skill_group_id, dbo.fnGenerateSkillXML(skill_group_id) FROM VW_DISTINCT_SKILLS_BY_SKILL_GROUP
|
RESULT
101 <SKILL><SKILL_ID>102…..
102 <SKILL><SKILL_ID>103…..
103 <SKILL><SKILL_ID>108…..
104 <SKILL><SKILL_ID>122…..
