Professional SQL Server™ 2005 Programming

11.7. What a Sproc Offers

Now that you spent some time looking at how to build a sproc, you probably want to ask the question about why you'd use them. Some of the reasons are pretty basic; others may not come to mind right away if you're new to the RDBMS world. Using sprocs has the following primary benefits:

• Enables you to create processes that require procedural action callable

• Enhances security

• Increases performance

11.7.1. Creating Callable Processes

As I've already indicated, a sproc is sort of a script that is stored in the database. The nice thing is that, because it's a database object, you can call to it. You don't have to manually load it from a file before executing it.

Sprocs can call to other sprocs (called nesting). With SQL Server 2005, you can nest up to 32 levels deep. This gives you thecapability to reuse separate sprocs much as you would use a subroutine in aclassic procedural language. The syntax for calling one sproc from anothersproc is exactly the same as it is calling the sproc from a script.

Note that local variables are just that: local to each sproc. Youcan have five different copies of @MyDate, one each in five differentsprocs, and they are all independent of each other.

11.7.2. Using Sprocs for Security

Many people don't realize the full use of sprocs as a tool forsecurity. As with views, you can create a sproc that returns a recordsetwithout having to give the user authority to the underlying table. Grantingsomeone the right to execute a sproc implies ...