Accepting multiple strings in CLR function

Question

I've got a stored procedure that calls a CLR and returns a table. Currently, it accepts a single string, but I would like to update it to accept multiple, perhaps in an array or something.

Currently, the C# function looks like:

public static IEnumerable ParseData(System.String data){ ... }

Trying to change the parameter to string[], System.String[], IEnumerable, and ArrayList all have failed. When trying to build with one of those, it changes a generated.sql file to

CREATE FUNCTION [dbo].[ParseData] (@data /* Error: Unsupported type. */)

In case it matters, what I'm going for is to be able to call my function in SQL like so:

SELECT * FROM clr_parseData((SELECT TOP 10 value FROM table))

Sounds you are thinking about a table valued parameter (which your query syntax is all wrong for). But CLR does not support passing entire datasets. You could pass in a delimited list but you can't pass in a collection like you are trying to. — Sean Lange
– Sean Lange, Commented Jan 11, 2017 at 21:00
@SeanLange I had a feeling that would be the answer. Guess I'll have to find a different way. — David Starkey
– David Starkey, Commented Jan 11, 2017 at 21:09
What does your ParseData method do? That is, given an array, would it just do the same thing for each element in that array? If so, it sounds like it's a CLR function that, depending on whether it's a scalar or table function, could be called either in the select list or with a cross apply (respectively). — Ben Thul
– Ben Thul, Commented Jan 11, 2017 at 22:59
@BenThul It parses a string of data based on various things in the string, returning a row for each item that is parsed out. You are correct that if it could be passed an array, it would essentially just loop through that array and run the same function on each string. — David Starkey
– David Starkey, Commented Jan 12, 2017 at 14:21

David Starkey · Accepted Answer · 2017-01-12 16:29:06Z

1

Based on your comments, you can use your existing function that has the signature of System.String (singular, not an array). You'd do it something like this:

select *
from (
    values 
        ('foo,bar'),
        ('bar,baz')
) as a(v)
cross apply dbo.ParseData(a.v) as p

Here, the table-valued constructor just serves as a short way for me to produce a set of data. SQL will apply your function on each value in the set and produce a data set. Assuming that dbo.ParseData does something simple like "parse a CSV" (that's what it'll do in my example!), the previous query will produce a data set like

foo,bar foo
foo,bar bar
bar,baz bar
bar,baz baz

edited Jan 12, 2017 at 16:29

David Starkey

1,8503 gold badges36 silver badges50 bronze badges

answered Jan 12, 2017 at 15:22

Ben Thul

32.9k4 gold badges52 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Accepting multiple strings in CLR function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related