Professional SQL Server™ 2005 Programming

Chapter 11. Getting Procedural: Stored Procedures and User-Defined Functions

OK, so here we are. We added a little bit of meat to the matter in terms of being able to actually code things in the last chapter, but this is where we start to get serious about the "programmer" aspect of things.

Let me digress for a moment though — temper your excitement about procedural work as relates to SQL. The reality is that the most important part of your work is probably already done — the design of your database. The way you build your schema is the very foundation of everything else that you do, so be careful to take the time you need in that seemingly simple stuff. Far too many database developers just throw together some tables and focus their time on the procedural logic — bad choice. As in all things in life, balance is the key.

Things have gotten more exciting from a "what you can" do perspective. In addition to the T-SQL programming constructs that we've always had, we now add .NET to the picture. In general, T-SQL will be the way we want to do things, but now we have the flexibility of adding .NET assemblies to not only create more complex procedures but also to create our own complex data types.

In this chapter, we're going to review how to create a basic stored procedure (sproc) and user-defined function (UDF) from the core elements of SQL Server, and then move quickly onto more complex procedural constructs, debugging. After we finish with the basic objects of SQL Server, we will ...