When a stored procedure creates an application using Microsoft®sql server™2000, the Transact-SQL programming language is the primary programming interface between the application and the SQL Server database. With Transact-SQL programs, you can store and execute programs in two different ways. You can store programs locally and create applications that send commands and process results to SQL Server, or you can store programs in SQL Server as stored procedures and create applications that execute stored procedures and process results.
Stored procedures in SQL Server are similar to procedures in other programming languages because stored procedures can:
Accepts input parameters and returns multiple values to the calling procedure or batch as an output parameter.
Contains programming statements that perform database operations, including calling other procedures.
Returns a status value to the calling procedure or batch to indicate success or failure (and the reason for failure).
You can run a stored procedure using the Transact-SQL EXECUTE statement. Stored procedures are different from functions because stored procedures do not return a value that replaces their name, nor can they be used directly in an expression.
The advantages of using stored procedures in SQL SERVER without using Transact-SQL programs that are stored locally on a client computer are:
Allows modular programming.
Simply create the procedure once and store it in the database, and then call the procedure any time in the program. Stored procedures can be created by people who specialize in database programming and can be modified independently of the program source code.
Allow faster execution.
If an operation requires a large number of Transact-SQL code or is repeated, the stored procedure will execute faster than Transact-SQL batch code. It is parsed and optimized when the stored procedure is created, and can be used in an in-memory version of the procedure after the process is first executed. Each time a Transact-SQL statement is run, it is sent repeatedly from the client and is compiled and optimized every time SQL Server executes the statements.
Reduce network traffic.
An operation that requires hundreds of lines of Transact-SQL code can be implemented by a separate statement that executes the process code, without the need to send hundreds of lines of code across the network.
Can be used as a security mechanism.
Even for users who do not have permission to execute statements directly in the stored procedure, they can be granted permission to execute the stored procedure.
SQL Server stored procedures are created with the Transact-SQL statement create PROCEDURE and can be modified using the ALTER PROCEDURE statement. A stored procedure definition consists of two main components: a description of the procedure name and its parameters, and the body of the procedure, which contains Transact-SQL statements that perform the procedure operation.
