Microsoft® SQL Server® 2008 Bible

Chapter 24. Developing Stored Procedures

IN THIS CHAPTER

• Creating and managing stored procedures

• Passing data to and from stored procedures

• Using stored procedures within ad hoc queries

• Executing stored procedures on linked SQL servers

Of all the possible SQL Server bad practices, I believe the worst is ad hoc SQL. The solution: stored procedures. Here's why.

Chapter 2, "Data Architecture," presented six databases objectives and the notion that with careful design and development, all six could be achieved. Architecting the database with stored procedures is critical to achieving five of the six objectives (all but availability):

• Extensibility: Using stored procedures is the best means of abstracting, or decoupling, the database. A stored procedure API contract will encapsulate the database and provide it with long-term extensibility.

• Performance: A well-written stored procedure is the fastest possible SQL Server code, it keeps the execution of data-centric code close to the data, and it's easier to index tune a database with stored procedures.

• Usability: It's easier for application programmers to make a stored procedure call and consume the result than it is to write ad hoc SQL.

• Data Integrity: A stored procedure developed by the database developer is less likely to contain data integrity errors, and easier to unit test, than ad hoc SQL code.

• Security: Locking down the tables and providing access only through stored procedures is a standard best practice for database development.

Of these ...