431
CHAPTER
17
Developing Stored Procedures
IN THIS CHAPTER
Creating and Managing Stored ProceduresPassing Lists and Tables to Stored ProceduresDefi ning Result SetsS
tored procedures are a way to store and reuse T-SQL code within the SQL Server Database
Engine. The procedure is stored as an object within SQL Server, and when created, can be
modifi ed and executed with simple T-SQL commands. The mechanics of using stored
procedures are straightforward, but they are powerful. From a basic SELECT statement to a multi-
statement, parameterized process, stored procedures enable you to defi ne and control interaction
with data. Stored procedures offer some compelling benefi ts:■ Consistency: A stored procedure encapsulates one or more T-SQL statements, enabling it to
be executed consistently across different areas of a system.
■ Maintainability: Stored procedures are maintainable because they are modular and inde-
pendent of the application or tool that they’re called from. Complex logic within a stored
procedure can be modifi ed with little to no impact on the application that calls it.■ (^) Security: Using stored procedures provides security benefi ts on two fronts: database and
application. From a database perspective, the stored procedure is an object. This means
that the ability to execute the procedure can be granted to specifi c users or groups. From
an application perspective, stored procedures provide an opportunity to validate input to a
T-SQL process before it is executed.
■ Performance: Stored procedure execution is a server-side process. This means that no mat-
ter how complex the procedure, only one statement is sent over the network. An applica-
tion that has embedded T-SQL code might have to use multiple round trips to the server to
achieve the same result.
c17.indd 431c17.indd 431 7/30/2012 5:39:32 PM7/30/2012 5:39:32 PM
http://www.it-ebooks.info