How to replace a table variable in T-SQL 2008 string contatenation

Question

I have a UDF running in SQL 2000 that uses a table var to return a comma delimited string from several rows of data:

CREATE FUNCTION [dbo].[ReturnVisitsTaskNos]
   (  @MainlinePlanID int )
   RETURNS varchar(1000)
AS
   BEGIN
      DECLARE @TaskNoTable table
         (  TaskNo varchar (15)  )
      DECLARE @TaskList varchar(1000)
      SET @TaskList = ''
      INSERT INTO @TaskNoTable
         SELECT TaskNo
         FROM MainlinePlanTask
         WHERE MainlinePlanID = @MainlinePlanID
      IF @@ROWCOUNT > 0
         UPDATE @TaskNoTable
            SET @TaskList = ( @TaskList + TaskNo + ', ' )
      RETURN substring( @TaskList, 1, ( len( @TaskList ) - 1 ))
   END

Is it possible to replace the table var code with something faster now that it is running on SQL 2008?

Thanks!

Answer 1

I can't vouch for the performance but if you use the FOR XML clause like this you will definitely get cleaner T-SQL in the function you have, without a temp table and cursor.

From the article:

Here is a very simple t-sql sample using FOR XML PATH() in order to sql concatenate strings values as a sample sql concatenation in MSSQL.

SELECT
  STUFF(
    (
    SELECT
      ' ' + Description
    FROM dbo.Brands
    FOR XML PATH('')
    ), 1, 1, ''
  ) As concatenated_string

Your updated function might look somethign like this:

CREATE FUNCTION [dbo].[ReturnVisitsTaskNos]
   (  @MainlinePlanID int )
   RETURNS varchar(1000)
AS
   BEGIN

    RETURN
      SELECT STUFF((
    SELECT
      TaskNo + ', '
    FROM MainlinePlanTask
    WHERE MainlinePlanID = @MainlinePlanID
    FOR XML PATH('')
    ), 1, 0, ''
  ) As concatenated_string

   END