Arrays in UserDefinedFunctions?

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

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

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

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

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:

• Split strings the right way – or the next best way
• Splitting Strings : A Follow-Up
• Splitting Strings : Now with less T-SQL
• Comparing string splitting / concatenation methods
• Processing a list of integers : my approach
• Splitting a list of integers : another roundup
• More on splitting lists : custom delimiters, preventing duplicates, and maintaining order
• Removing Duplicates from Strings in SQL Server
• http://www.sommarskog.se/arrays-in-sql-2005.html

On SQL Server 2016 or above, though, you should look at STRING_SPLIT() and STRING_AGG():

• Performance Surprises and Assumptions : STRING_SPLIT()
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #2
• SQL Server v.Next : STRING_AGG() performance
• Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions