Microsoft SQL Server 2012 Bible

Chapter 17

Developing Stored Procedures

In This Chapter

Creating and Managing Stored Procedures

Passing Lists and Tables to Stored Procedures

Defining Result Sets

Stored 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 modified 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 define and control interaction with data. Stored procedures offer some compelling benefits:

• 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 independent of the application or tool that they're called from. Complex logic within a stored procedure can be modified with little to no impact on the application that calls it.
• Security: Using stored procedures provides security benefits 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 specific 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