Passing string with quotes " 'VALUE1', 'VALUE2', 'VALUE3' " to a table-value

I have a problem passing a string containing single quotes to a table-valued-function

ex:

SET QUOTED_IDENTIFIER OFF

SELECT * FROM myFunc(" 'VALUE1', 'VALUE2', 'VALUE3' ")

GO

--

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

G

ALTER FUNCTION dbo.myFunc(@.VendorID CHAR(100) )

)RETURNS TABLE AS

RETURN (

SELECT * FROM myTable WHER Vendor IN (@.VendorID))

)

It works if I pass only 'VALUE1'

but not if I pass " 'VALUE1', 'VALUE2', 'VALUE3' "

nor if I use 2 single quotes to delimit each string.

The same code will work if MyFunc is a STORE PROC instead of a function

EXEC myProc '''990001'',''990002''' will work (notice 3 quotes at the begining, then 2, then 2 then 3)

... but I need it to be a function !!!

Help please

You can't do what you want I am afraid.

Have a look at this article on what you want to do

http://www.sommarskog.se/arrays-in-sql.html