1

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 there is a direct approach of setting the string argument as it is, and using the SQL IN operator in a UDF.

Cheers.

Terman
  • 735
  • 1
  • 7
  • 18
  • I do not get it , Do You want to get string 'gold,silver,copper' from select or you want to process that string in where clause – adopilot May 16 '11 at 08:24
  • Sorry, I'd like to process the String in my Where clause in a UDF – Terman May 16 '11 at 08:36

1 Answers1

2

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',','))
adopilot
  • 4,340
  • 12
  • 65
  • 92
  • Nice. A similar approach is the second one [on this page](http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx). – kmote Jun 20 '12 at 18:33