Chapter 12

Stored Procedures


  • 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 ...

Get Beginning Microsoft® SQL Server® 2012 Programming now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.