CLR SQL Server UDF Question

Question

I am trying to create a CLR UDF that takes in a string value seperated like "Mike|John|Smith". In the UDF I parse out the values and return them in a DataTable. The C# code builds fine and I can create the assembly in SQL pointing to the DLL without a problem.

Problem comes in when I am trying, according to this source, to tell SQL Server how to match up a Transact SQL request with a CLR function. http://www.setfocus.com/technicalarticles/clrfunctionforsqlserver_2.aspx

or if I even have to do that? Basically I want to take in a string, parse out the seperated values and put them in a DB Table with 3 different columns, FN, MN, LN. using a CLR UDF.

Answer 1

You need to have a SQL stored procedure or function that calls the Assembly. The parameters to the SQL stored procedure and the .NET function need to match. So for example, if your .NET assembly has the following function:

[SqlProcedure]
public static void ProcessData(string myString_)
{
}

your SQL Stored Procedure or Function would have to look like the following:

CREATE PROCEDURE dbo.ProcessMyData (@InputText AS NVARCHAR(MAX))
AS EXTERNAL NAME SqlAssemblyName.ClassName.ProcessData
GO