Stored Procedures - SQL

Stored Procedures – What are they?
Stored procedure is a set of pre-defined Transact-SQL statements, used to perform a specific task. There can be multiple statements in a stored procedure, and all the multiple statements are clubbed in to one database object.

How to create a stored procedure?
Creating a stored procedure is as easy as running the “Create Procedure” statement followed by the SQL script. You can run your Create Procedure statement from the SQL Query Analyzer, or can use the New Procedure menu item in the Enterprise Manager.
The simplest skeleton of a stored procedure.
CREATE PROC procedure_name [ { @parameter data_type } ] AS sql_statement

stored procedure includes
1. A CREATE PROC (CREATE PROCEDURE) statement;
2. The procedure name;
3. The parameter list
4. And the SQL statements.

Advantages!
1. Performance
All the SQL statements, that you send to your database server passes through a series of actions, called execution. These are the steps that your SQL statement passes through before the data is returned to the client.
2. Security
Stored procedures provide significant benefits when it comes to security. By using a stored procedure, you can grant permissions to certain users to access data, reducing the immense coding that you need to do in your client applications. This is one of the best ways to control access to your data.
3. Modifications/Maintenance
If you use stored procedures for database access, any change in the database can be reflected on to the client application without much effort. This is because you know exactly where the data is accessed from, and you also know exactly where you need to alter. This means no scuba diving in to thousands of lines of source code to identify areas where you need to alter and no headache of re-deploying the client application.
4. Minimal processing at the client.
When creating a client/server application, normally it was the client who took care of the integrity of data that went in to the database. Managing Primary Keys, Foreign keys, cascaded deletion everything was done by the client, and the database server just had to store data given by the client.
5. Network traffic
Client applications always have to request/send data from the database server. These data are sent as packets, and travel through the network to the server.