If you work actively with SQL Server and do most of the data operations using stored procedures, then I bet that at least once, you wanted to pass an array of values as a parameter to a stored procedure (or function). Just like you, I had the same situation. I’ve seen many approaches to this problem and all of them has their pros and cons. I’ve implemented a solution using a CLR Stored Procedure, that I find quite practical and well performing.

As I mentioned in my previous posts, CLR objects on SQL Server can be very useful in certain scenarios. Here I will show you how to create, deploy and test a simple CLR stored procedure. This stored procedure will return a file list from the file system upon the specified path.

Visual Studio 2008 comes already with a project template for this purpose. In this example I will show you how to create an empty project.

SQL CLR is the ability to create SQL Server programmability objects using .NET languages and framework.

Common Language Runtime (CLR) is the runtime environment that manages the interpretation, compilation and execution of .NET program code. It also provides services such as memory management, exception management, debugging and security.

Last Thursday, November 5 2009, I had the honor to be the speaker of one of the sessions of the PR PASS (Professional Association for SQL Server, Puerto Rico Chapter) meetings. I made a short presentation called “SQL CLR Basics”. In it, I showed the attendants the basics on how SQL works and how to get started with it.