Passing Enumerables to a SQL Stored Procedure

Passing Enumerables to a SQL Stored Procedure

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.

I neglected to take a screenshot of this bit when mocking the above code, so this is from a project I am working on! Sorry for the differing paramter names, but @LookupList would be @Table, and [FSD].[DetailLookup] is [dbo].[MyTableType].
James Chorlton

About James Chorlton

I am a software developer from the South-West of England; I mostly work in .NET (c#) creating desktop, web, service, and backend software for the Legal and Health markets.

Comments