Chapter 16. Table-valued parameters
A major goal of the new Transact-SQL (T-SQL) features in SQL Server 2008 is to reduce the amount of code you need to write for common scenarios. Many new language features simplify code, and table-valued parameters probably do so most dramatically.
Such an innocuous name for a radical new feature! It’s the sort of thing that only a geek could love: the ability to pass a table to a procedure. It’s a simple enhancement but will change the way you think about programming SQL Server forever. If you’ve ever passed a comma or other delimited list of data values to a stored procedure, then split them up, and processed them, or bumped up against stored procedure parameter limits, you know the pain that is now forever gone. In this chapter I’ll explore the syntax and use of this new T-SQL feature, both in SQL Server code as well as client code. By the end of the chapter, you’ll wonder how you ever programmed without it!
Before SQL Server 2008, there was no easy way to pass data containers—arrays, DataSets, DataTables, and so on—to stored procedures and functions. You could pass single scalar values with no problem, although if you had to pass many parameters you might run into the limit on parameters, which is 2,100. Objects like arrays, in-memory tables, and other constructs are not the kind of set-based objects that T-SQL deals with. Yet sometimes it is necessary to pass data containers to a code module.
