Convert a comma separated nvarchar to a list that can be used in any T-SQL

Many times in many projects I had to filter some records in a list by selecting not one but multiple categories. This has been a nightmare to be done in a stored procedure since you had to have a limited numbers of parameters and it would be inefficient to have @category1, @category2, etc. What you wanted to do is have one value that you would name @categories and then pass there any number of values separated by a character.

Now this can be achieved by using an intermediate stored procedute that converts a string delimited by a char to a table list of values. This stored procedure is the following:

CREATE FUNCTION dbo.spListToTable
(
	@List VARCHAR(MAX),
	@Delim CHAR
) RETURNS @ParsedList TABLE ( item VARCHAR(MAX) )
AS
BEGIN
	DECLARE @item VARCHAR(MAX), @Pos INT

	SET @List = LTRIM(RTRIM(@List))+ @Delim
	SET @Pos = CHARINDEX(@Delim, @List, 1)

	WHILE @Pos > 0
	BEGIN
		SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
		IF @item <> ''
		BEGIN
			INSERT INTO @ParsedList (item)
			VALUES (CAST(@item AS VARCHAR(MAX)))
		END

		SET @List = RIGHT(@List, LEN(@List) - @Pos)
		SET @Pos = CHARINDEX(@Delim, @List, 1)
	END
	RETURN
END
GO

Then in an example as the one described above you would use it like this :

SELECT *
FROM [tblList]
WHERE [categoryID] IN (
	SELECT [item] FROM dbo.spListToTable('1,2,3',',')
)

That would return you all the items that are in categories 1,2,3 !