Sean Lange (5/1/2014)
Just an fyi...the code you posted is what is known as a multi statement table valued function (mstvf). These are very poor performers. In fact, a typical scalar function or even a cursor will generally perform better.I triple the suggestion to take a look at Jeff's splitter. It is fast!!!
Thanks for the suggestion and the advice.
I have taken a look at the splitter and it works very well.
However the purpose of this exercise is for my own development, nothing will be implemented as a business solution, I'm just trying to find the best way to get the outcome.
If i was to change the type of function to scalar, would this end up giving the correct dataset required?
I have also been looking at using a row_number function to define the data and then to iterate through each line, however i only return the final line (see below)
/****** Object: UserDefinedFunction [dbo].[SplitList] Script Date: 05/01/2014 16:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[SplitCust3]
(
@CommaDelimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
Declare @CustomerCode [varchar](max)
Declare @I INT
Select @I = ROW_NUMBER() OVER (Order by __CODE), @CustomerCode = [Customer Code]
From PROJECT
Where [Customer Code] like '%,%'
------------------------------------------------------------------------
------------------------------------------------------------------------
Set @I = 1
WHILE @I =1-- 10
BEGIN
DECLARE @sTemp [varchar](max)
SET @sTemp = ISNULL(@CustomerCode,'')
+ @CommaDelimiter
While LEN(@sTemp) > 0
Begin
INSERT INTO @Table
SELECT --@Project as Project,
SubString(@sTemp,1,
CharIndex(@CommaDelimiter,@sTemp)-1) as customer
SET @sTemp = RIGHT(@sTemp,
LEN(@sTemp)-CharIndex(@CommaDelimiter,@sTemp))
END
Set @I = @I+1
END
RETURN
END