Stored Procedure Basics
Stored procedures in Microsoft SQL Server are similar to procedures in other programming languages in that they can:
- Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
- Contain programming statements that perform operations in the database, including calling other procedures.
- Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).
You can use the Transact-SQL EXECUTE statement to run a stored procedure. Stored procedures are different from functions in that they do not return values in place of their names and they cannot be used directly in an expression.
The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored locally on client computers are:
- They are registered at the server.
- They can have security attributes (such as permissions) and ownership chaining, and certificates can be attached to them.
Users can be granted permission to execute a stored procedure without having to have direct permissions on the objects referenced in the procedure. - They can enhance the security of your application.
Parameterized stored procedures can help protect your application from SQL Injection attacks. For more information see SQL Injection. - They allow modular programming.
You can create the procedure once, and call it any number of times in your program. This can improve the maintainability of your application and allow applications to access the database in a uniform manner. - They are named code allowing for delayed binding.
This provides a level of indirection for easy code evolution. - They can reduce network traffic.
An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
See Also
Concepts
Other Resources
Understanding Stored Procedures