In my previous post, I discussed the function used to split a string into a table of tokens. Very useful instead creating a cursor.

Now, with SQL 2005/2008, it became easier and faster to do the same job using XML parsing.

I found this function online, written by Jacob Sebastian which is much faster than my old one:

CREATE FUNCTION dbo.SplitString
(
   @str VARCHAR(MAX),
   @delimeter CHAR(1)
)
   RETURNS @ret TABLE (Token VARCHAR(MAX)) AS
   BEGIN
      DECLARE @x XML
      SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'
      INSERT INTO @ret
      SELECT x.i.value('.', 'VARCHAR(MAX)') AS token
      FROM @x.nodes('//t') x(i)
      RETURN
   END

I used it to update a table, passing the list of values coming from a ColdFusion list and it was very fast. Here is a sample code:
USE [myDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[updateBooks]
   @prodIdList varchar(max)
,   @purchaseDate datetime
,   @invoiceNo varchar(50)
AS
BEGIN
   SET NOCOUNT ON;
   update p set
      p.invoiceNo = @invoiceNo
   ,   p.lastPurchaseDate = @purchaseDate
   from tbl_purchases p
   inner join dbo.splitString(@prodIdList,',') temp on temp.token = p.prodId
END

The product ID list is passed in @prodIdList, and the inner join matches with p.prodId existing in the tbl_purchases table