Arrays in UserDefinedFunctions?

Question

I created a User Defined Function with VB.NET and want to use that function in SQLServer. Everything seems to work fine.

Now I want to use an array as parameter. Is it possible?

Example of my test method (without parameters):

Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function TestFunction() As SqlString
     ' Add your code here
     Return New SqlString("Hello World")
    End Function
End Class

Can someone give me a working example or some ideas for a workaround?

Thanx

Answer 1

Arrays are not possible. Pass XML instead (in XML you can easily represent an array)

Answer 2

You'll need to use either a delimited string or XML and then parse the parameter in your function.

Answer 3

Check this out: it is an example of how to use xml in order to emulate array support:

http://ayende.com/Blog/archive/2007/07/10/Sending-arrays-to-SQL-Server-Xml-vs.-Comma-Separated-Values.aspx

This is the punchline for your SQL:

DECLARE @ids xml
SET @ids = '<ids>
     <id>ALFKI</id>
     <id>SPLIR</id>
</ids>'
SELECT * FROM Customers
WHERE CustomerID IN (SELECT ParamValues.ID.value('.','NVARCHAR(20)')
FROM @ids .nodes('/ids/id') as ParamValues(ID) )

In you VB.Net code (assuming you work on .Net Framework 3.5) you should use LINQ to XML in order to generate the XML from your Array. It is pretty easy. You can find an introductory example here:

http://blogs.msdn.com/wriju/archive/2008/02/13/linq-to-xml-two-important-classes-xelement-and-xattribute.aspx

Answer 4

SQL Server has no idea what an array is, so you cannot pass one in to a stored procedure or function. There are alternatives to XML in terms of splitting a list that is composed of a string to simulate an array. Some ideas:

http://is.gd/4FS2M (first 4 hits)

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