function to use comma seprated values in to a table so that it can be use in IN statement in sql
ALTER FUNCTION [dbo].[fnNTextToIntTable] (@Data nvarchar(4000))
RETURNS
@IntTable TABLE ([Value] NVARCHAR(500) NULL)
AS
BEGIN
SET @Data=Replace(@Data,'$',',')
DECLARE @Ptr int, @Length int, @v nchar, @vv nvarchar(10)
SELECT @Length = (DATALENGTH(@Data) / 2) + 1, @Ptr = 1
WHILE (@Ptr < @Length)
BEGIN
SET @v = SUBSTRING(@Data, @Ptr, 1)
IF @v = ','
BEGIN
INSERT INTO @IntTable (Value) VALUES (CAST(RTRIM(LTRIM(@vv)) AS NVARCHAR(500)))
SET @vv = NULL
END
ELSE
BEGIN
SET @vv = ISNULL(RTRIM(LTRIM(@vv)), '') + @v
END
SET @Ptr = @Ptr + 1
END
-- If the last number was not followed by a comma, add it to the result set
IF @vv IS NOT NULL
INSERT INTO @IntTable (Value) VALUES (CAST(RTRIM(LTRIM(@vv)) AS NVARCHAR(500)))
RETURN
END
