SQL Server and CLR, batching SqlFunction

I have a CLR function that returns "n" rows with random data. For example, to prime an empty table with 100 rows of test data I could write

INSERT INTO CustomerInfo(FirstName, LastName, City...)
     SELECT FirstName, LastName, City... 
     FROM MyCLRFunction(100)

This would return 100 "customers" with random information. If I were to call this with a very high number I would get an out of memory error, since the entire dataset is created before it gets sent to the caller. I can, of course, use the SqlPipe object and send rows as they are created but as far as I can tell you can only use this approach with SqlProcedures. That would mean that I can't use an INSERT INTO approach since you can't SELECT from a stored proc.

I'm hoping that I've just missed something here and that it is actually possible to combine SqlPipe.SendResultRow with a function, or that someone has a clever workaround.

I could leave it as a proc and have that proc put these records into a session-scoped temporary table. Then the caller could use that table in their SELECT clause but I'm hoping for the best of all worlds where I can provide a nice, clean syntax to the caller and still scale to a large number of records.

Frankly, the original solution is probably "good enough" since we will probably never want this much test data and even if we did we could run the INSERT statement multiple times. But I'm trying to get a full understanding of CLR integration and wondering how I would address this if a similar use case presented itself in a business scenario.

Answers


Looking into streaming SQLCLR table valued functions - http://msdn.microsoft.com/en-us/library/ms131103.aspx

You basically return an IEnumerable to SQL Server and let it consume it, thereby not needing to materialize all the results before returning them.


I found a solution. Instead of returning the entire list of items, the solution was to use

yield mything;

This causes the FillRowMethod to be fired for each entity processed, effectively streaming the results back to the caller.

Glad to have this figured out but a little embarrased by how simple the final solution was.


Need Your Help

Clearcase find version based on hyperlink “HlinkToText” attribute value

clearcase cleartool

We are using base ClearCase with ClearQuest integration and whenever a checkin is made, a hyperlink with the WorkRequest number in ClearQuest is attached to the checked in version. I could look at ...

Android :: Servlets, I'm trying to flush a stream to a servlet

android servlets

i want to send an input stream from android to a servlet, but i dont think i can see the right way to communicate with the stream in android and the stream in the servlet, here is the sample for bo...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.