I already knew how to do this, but it’s what I was working on yesterday so I figured I’d regurgitate it for others who may not know. This technique is especially handy when you find yourself doing a bunch of individual database queries, such as a “Get By ID” call on 5 specific items.
As of SQL Server 2008, you can create what are called “User Defined Table Types”. These are basically table definitions that can be used as variables in your other stored procedures or user-defined functions. To create one, it’s as simple as a type definition:
1 2 3
This will create a user-defined table type called MyTableType – you can verify this in SQL Server Management Studio by going to Programmability –> Types –> User-Defined Table Types. Now that we have one created, what can we do with it? First off, you can use it as an input variable to your stored procedure.
Once you have that table passed in, you can treat it as any other table in SQL – run select queries against it, insert, modify, and delete data, etc. As I lead the article with, this is a great way of passing a collection of data into a stored procedure.
If you want to actually execute this stored procedure, there’s a few different things you can do. In SQL, you’d have to declare and populate the table before calling EXEC.
1 2 3 4 5 6
To do the same thing through C#, you’ll have to create a DataTable and pass it as a parameter to your SQL Command.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16