IV.2.1. Why You Need Stored Procedures and Functions

Both stored procedures and functions can significantly add to the performance and usability of SQL Server 2008. By mastering these objects, you go a long way toward mastering programming within SQL Server.

Out of the box, SQL Server 2008 includes many system stored procedures and built-in functions. These are already optimized, and they provide two significant benefits:

  • They're ready to use without any programming by you. All you have to do is identify the object that you want to use and plug it into your code. You don't have to understand the underlying code, and you especially don't have to program the underlying code. All you need to know is what's available and how to use it.

  • They're optimized. Microsoft has already optimized the code for the built-in objects, which means that they run quicker and use the least possible amount of resources.

There will be times when the existing stored procedures and functions don't meet your needs. In these instances, you can create your own objects. These are referred to as user-defined (such as user-defined stored procedures and user-defined functions).

IV.2.1.1. Understanding stored procedures

Stored procedures are groups of T-SQL statements put together into a single object. They can be composed of a single T-SQL statement, such as a SELECT statement, or hundreds of lines of T-SQL.

For example, you might need to write a T-SQL batch file that makes the appropriate entries in a database ...

Get Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies® now with O’Reilly online learning.

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