Use LINQ to SQL results inside SQL Server stored procedure

Question

Note: I'm not trying to call a SQL Server stored proc using a L2SQL datacontext.

I use LINQPad for some fairly complex "reporting" that takes L2SQL output saved to an Array and is processed further.

For example, it's usually much easier to do multiple levels of grouping with LINQ to Objects instead of trying to optimize a T-SQL query to run in a reasonable amount of time.

What would be the easiest way to take the end result of one of these "applications" and use that in a SQL Server 2008 stored proc?

The idea is to use the data for a Reporting Services Report, rather than copying and pasting into Excel (manual labor). The reports need to be accessible on the report server (not using the Report Server control in an application).

I could output CSV and read that somehow via command line exec, but that seems like a hack. Thanks for your help.

Answer 1

SQL Server 2005 and later allow you to write stored procedures in .NET. That way you can use LINQ in a report.

The official name is "CLR Stored Procedure". Here's a nice introduction.

Answer 2

There are two possibilities, both of which will require you to use "raw" ADO.NET:

1. Use the SqlBulkCopy class to insert the data into a staging table (could be a temp table) and code the Stored Procedure to read from that table; or

2. Write the data into a DataSet or DataTable and use a Table-Valued Parameter to pass it into the Stored Procedure.

The second approach is "cleaner" than the first, but won't perform as well if you have a very large amount of data (more than several hundred rows).