what is stored procedure?

A Stored Procedure is a set of SQL statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity (information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written one time).
Advantage of stored procedure

a) Stored procedure allows modular programming.You can create the procedure once, store it in the database, and call it any number of times in your program.

b) Stored Procedure allows faster execution. If the operation requires a large amount of SQL code is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

c) Stored Procedure 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.

d) Stored procedures provide better security to your data. Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.

In SQL we are having different types of stored procedures are there

a) System Stored Procedures
b) User Defined Stored procedures
c) Extended Stored Procedures

Calling stored procedure in asp.net

SqlConnection con = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
con.Open();
dr= cmd.ExecuteReader();
whiledr.Read()
{
// Data is accessible through the DataReader object here.
}
con.Close();