passing a comma-delimited string of values to a User Defined Function: towards using the "IN" tsql operator

In TSQL (SQL SERVER 2008) how would I pass a -comma delimited - list of string values- eg. 'gold,silver,copper' to a User-defined function that specifies a varchar parameter.

I realize that if I define a varchar parameter in my UDF say @commodityList that takes the the list 'gold,silver,copper', I'm unable to use the IN operator. ie

SELECT * FROM Prodn where commodity in (@commodList).

I've read some articles on taking the Table-Valued Paremeter approach however I reckon going down that path would mean splitting my string into chunks - in order to Insert into a Table-valued Parameter e.g:

DECLARE @mylist commodity_list_tbltype

INSERT @mylist VALUES('gold'),('silver'),('copper')

I'm wondering if 开发者_JS百科there is a direct approach of setting the string argument as it is, and using the SQL IN operator in a UDF.

Cheers.

You can create udf function for spiting sting into table

CREATE FUNCTION [dbo].[fn_ado_param] (@ado nvarchar(4000), @Delim char(1)= ',')
RETURNS @VALUES TABLE (ado nvarchar(4000))AS
   BEGIN
   DECLARE @chrind INT
   DECLARE @Piece nvarchar(4000)
   SELECT @chrind = 1
   WHILE @chrind > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim,@ado)
         IF @chrind > 0
            SELECT @Piece = LEFT(@ado,@chrind - 1)
         ELSE
            SELECT @Piece = @ado
         INSERT @VALUES(ado) VALUES(@Piece)
         SELECT @ado = RIGHT(@ado,LEN(@ado) - @chrind)
         IF LEN(@ado) = 0 BREAK
      END
   RETURN
END

after that you can use it in your IN clause

select * from dbo.fn_ado_param('gold,silver,copper',',')

or in other plces

select * from anytable where somefield in (select * from dbo.fn_ado_param('gold,silver,copper',','))