Stored Procedure (from BOL ):

When using Microsoft® SQL Server™ 2000 to create an application, the Transact-SQL programming language is the main programming interface between the application and the SQL Server database. When you use a Transact-SQL program, you can store and execute the program in two ways. You can store programs locally and create applications that send commands to SQL Server and process the results. You can also store programs as stored procedures in SQL Server, create an application that executes the stored procedure and processes the result.
The stored procedures in SQL Server are similar to those in other programming languages because they can:
Accept input parameters and return multiple values to the call process or batch process in the form of output parameters.
Contains programming statements that execute database operations (including calling other processes.
Return a status value to the call process or batch process to indicate success or failure (and cause of failure ).
You can run the stored procedure using the EXECUTE statement. Stored procedures are different from functions because stored procedures do not return values that replace their names, nor can they be directly used in expressions.
The advantages of using the stored procedures in SQL Server instead of using the locally stored Transact-SQL program on the client's computer are as follows:
Modular programming is allowed.
You only need to create a process once and store it in the database. Then you can call the process any time in the program. Stored procedures can be created by people with expertise in database programming and can be modified independently of the program source code.
Allows faster execution.
If an operation requires a large number of Transact-SQL code or repeated execution, the stored procedure is faster than the execution of the Transact-SQL batch code. The stored procedure will be analyzed and optimized when it is created, and the memory version of the process can be used after the first execution of the process. Each time you run a Transact-SQL statement, it must be repeatedly sent from the client and compiled and optimized every time the SQL Server executes these statements.
Reduce network traffic.
An operation that requires hundreds of rows of Transact-SQL code is implemented by a separate statement that executes the process code, without sending hundreds of lines of code in the network.
It can be used as a security mechanism.
Users who do not have the permission to directly execute statements in a stored procedure can grant them the permission to execute the stored procedure.
SQL Server stored procedures are created using the create procedure statement of Transact-SQL statements and can be modified using the ALTER PROCEDURE statement. The stored procedure definition consists of two main components: the process name and its parameter description, and the body of the process (including the Transact-SQL statement for executing the process operation ).