Chapter 12
Stored Procedures
WHAT YOU WILL LEARN IN THIS CHAPTER:
- What a stored procedure is, and when you’ll want to use one
- How to create, alter, and drop stored procedures
- Ways to pass input and output parameters
- Error handling
- Performance considerations
- How to use the debugger
Ah, the good stuff. If you’re a programmer coming from a procedural language, this is probably the part you’ve been waiting for. It’s time to get down to the main variety of code of SQL Server, but before you get going too far down that road, I need to prepare you for what lies ahead. There’s probably a lot less than you’re expecting and, at the very same time, a whole lot more. You’ve got the venerable T-SQL stored procedure capabilities, plus since SQL Server 2008, you’ve had .NET support — giving you a veritable “ooh la la!” of possibilities.
You see, a stored procedure, sometimes referred to as a sproc (which I usually say as one word, but I’ve sometimes heard pronounced as “ess-proc”), is really just something of a script — or more correctly speaking, a batch — that is stored in the database rather than in a separate file. Now this comparison is not an exact one by any means — sprocs have things, such as input parameters, output parameters, and return values, that a script doesn’t really have, but the comparison is not that far off either.
For now, SQL Server’s only “programming” language continues to be T-SQL, and that leaves you miles short of the kind of procedural horsepower that you expect ...
