I have the following UDF.
CREATE FUNCTION [dbo].[udf_GenerateVarcharTableFromStringList]
(@list varchar(MAX),
@delimiter char(1) = N',')
RETURNS @tbl TABLE ([Value] varchar(200))
WITH SCHEMABINDING
AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(4000)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@delimiter,@list)
IF @chrind > 0
SELECT @Piece = LEFT(@list,@chrind - 1)
ELSE
SELECT @Piece = @list
INSERT @tbl([Value]) VALUES(@Piece)
SELECT @list = RIGHT(@list,LEN(@list) - @chrind)
IF LEN(@list) = 0 BREAK
END
RETURN
END
I call this function with the following code example from a where clause in my sprocs:
WHERE u.[Owner] IN
(SELECT [VALUE]
FROM dbo.udf_GenerateVarcharTableFromStringList(@Owners, ','))
I use this where statement in sprocs that I use for reporting services multivalue parameters. When a user selects jsut blank for the value it returns the correct data but if a user selects a blank value and another value that actualyl has text it does not return the blank value owners anymore, just the owners from the text? Any ideas? I take it something is wrong with the function?