I have been asked the question “Can you pass an enumerable to a procedure?” or “How do you pass a table to a stored procedure” several times in the past. The simple answer is “yes”, the slightly more complex answer is “yes, and this is how”!
How to pass an Enumerable to a SQL Stored Procedure in .NET
Although my example code here is in C# the same process applies to other .NET languages.
You can indeed pass an enumerable object to your Stored Procedure by using a special type of SQL object called a “User Defined Table Type”. UDTTs can be used to create tempory tables in a SQL Query without the need to fully write out the TABLE statement; in much the same way they can be used to pass a tempory table to a stored procedure. The only restriction is that the parameter passed into the query must be marked as read-only.
An example SQL statement to create a User Defined Table Type is as follows:
An example of using that type in a procedure is also as follows:
Now the question is how to execute a procedure from a .NET application passing in your enumerable? The easiest way is to create a Data Table with a matching schema to the UDTT.
You can then execute your stored procedure in the normal way, passing the table to the parameter on creation (or in your preferred way); just make sure you also set the TypeName of the parameter and set the type to Structured.