This is the code for the function:
CREATE FUNCTION dbo.Split_Multi_Value_Parameters
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(max),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item NVARCHAR(100) collate database_default )
AS
BEGIN
DECLARE @tempItemList NVARCHAR(max)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(max)
SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/