When deciding on where the cutoff should be between my Beginning and Professional titles, this was, perhaps, the most difficult area for me to reconcile. The thing is, how much a supposed "SQL Server jock" knows about things beyond basic DML really varies a lot, so what exactly qualifies someone as ready to do the "Professional" level title?
In this chapter, I'm going to assume that you already know the basics of stored procedures and user-defined functions (the differences between them, types of SQL-based user-defined functions, parameterization, and basic control of flow statements). After all, if they are "the basics," then they seem more appropriate for a beginning title (and, indeed, I cover them at length in Beginning SQL Server 2008 Programming). So what, then, is this chapter all about? Well, it's about all the things that go beyond the basics. In this section, we'll cover:
OUTPUT parameters (often misunderstood by even advanced SQL programmers)
Error handling (again, I cover this somewhat in the Beginning title, but it's so often misunderstood even amongst advanced SQL programmers that it deserves revisiting)
Table-valued parameters (new with SQL Server 2008)
.NET-based stored procedures and user-defined functions
Even paring out the so called basics, there is a lot to be covered, so let's get to it.
Most of the concepts provided in this chapter apply relatively equally to both stored procedures and user-defined functions.