Media-table
MediaID int
Name nvarchar(20)

MediaGroup-table
MediaGroupID int
Recipients ntext   -- contains comma separated MediaID's (CSV)
                   -- like 1,4,5,7,8,99,293,423

SELECT Media.Name FROM Media WHERE MediaID IN ((SELECT Recipients FROM MediaGroup WHERE MediaGroupID = 7))

Error 279: the text, ntext, and image data types are invalid in this subquery or aggregate expression.

quote:

---

Originally posted by nr

???
If that did compile would you expect it to work?
Try it with a convert varchar.

---

CREATE FUNCTION CSV2Table_fn(@PInStrSource varchar(8000) = NULL,@pinChrSeparator char(1))

/* 
	DECLARE @CSV as nvarchar(10)	-- this must be big enough to hold all the separated values
	SET @CSV = '7;9;13'
	SELECT ID,Name FROM Users WHERE ID IN 
		(
			SELECT ItemValue FROM CSV2Table_fn(@CSV)
		)

	Output
	ID	Name
	7	Eric Johnson
	9	John M.
	13	Fooboo
*/

	RETURNS 
		@ARRAY TABLE(ItemValue varchar(1000))
AS
BEGIN
--	DECLARE @pInChrSeparator char(1) 
	DECLARE @CurrentStr varchar(2000)
	DECLARE @ItemStr varchar(200)
	
	SET @CurrentStr = @PInStrSource
	 
	WHILE Datalength(@CurrentStr) > 0
	BEGIN
		IF CHARINDEX(@pInChrSeparator, @CurrentStr,1) > 0 
			BEGIN
	           	SET @ItemStr = SUBSTRING (@CurrentStr, 1, CHARINDEX(@pInChrSeparator, @CurrentStr,1) - 1)
	            SET @CurrentStr = SUBSTRING (@CurrentStr, CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1, (Datalength(@CurrentStr) - CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1))
				INSERT @ARRAY (ItemValue) VALUES (@ItemStr)
	   		END
		 ELSE
		 	BEGIN                
				INSERT @ARRAY (ItemValue) VALUES (@CurrentStr)	 		
	           	BREAK;
	       	 END 
	END
	RETURN
END

DECLARE @Recipients varchar(8000)
SELECT @Recipients = CAST(Recipients as varchar(8000)) FROM MediaGroup WHERE MEDIAGroupID = 7
SELECT Media.Name FROM Media WHERE MediaID IN (SELECT ItemValue FROM CSV2Table_fn(@Recipients,','))