Summary

Stored procedures are one of the premier features of Microsoft SQL Server. They provide a number of benefits over using ad hoc SQL, including faster performance, restricted, function-based access to tables, protection of your application code from database changes, and the ability to simplify complex tasks into a simple stored procedure call.

It is important, however, to understand the various capabilities and limitations of stored procedures before writing much stored procedure code. Poorly written procedures will make the server appear to run sluggishly and inefficiently. Well-written procedures will run efficiently and solidly. Following the guidelines and tips presented in this chapter should help you write efficient and solid stored procedures.

If you have additional SQL code that must be executed every time a table is modified, you can put that code in stored procedures and require all applications to use the stored procedures to perform the modifications. However, this doesn't prevent a dbo or system administrator from accessing the table directly and modifying its contents without using the stored procedure. This can lead to data integrity problems. To ensure that the code is executed every time the data is modified, you can use triggers. Triggers are essentially a special type of stored procedure that is executed automatically when data modifications are performed on the table. For more information on defining and using triggers in SQL Server, you might want to read the next chapter now.