Where a stored procedure is a special feature of a database management system (DBMS), And a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database because it is a pre-compiled form and so that it can be easily shared by a number of programs. If we can talk about the use of stored procedures it can be very helpful in controlling access to data (end-users may enter or change data but do not changed/write procedures), protected of data integrity ( the information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written one time).

Another Definition

The stored procedure is a set of SQL commands that have been compiled and stored on the database server. Once the stored procedure has been "stored", client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again. And also, Stored procedures improve performance by reducing network traffic and CPU load.

This is used to perform tasks within the database, whether it be to INSERT, UPDATE, DELETE, SELECT, send return values, send output parameters, send e-mail, call command-line arguments, encapsulate business logic, enforce data integrity, or any combination thereof.

Main Features:

•    It is in compiled form.
•    End-users may enter or change data but do not write procedures
•    Information is entered in a consistent manner.
•    Statements in a stored procedure only need to be written one time.
•    It improves performance by reducing network traffic and CPU load. 

It same as the user-defined function (UDF’s) but there are some difference between them

Main Benefits of the stored procedure: 

Precompiled Execution: The stored procedure is stored in the database as a compiled form. So it is compiled only one time.

When a stored procedure is called may times so it gives fast and better results.

Reduced server and client traffic so it reducing the network traffic and CPU load.

It removes the SQL Injection problem. Etc. 

How to use the Stored Procedure in SQL Server 

Select query in Stored Procedure

Step 1: we write this statement on SQL Server (in query window). 

CREATE procedure [dbo].[ProcedureName]
@Parameter1 int,
@Parameter2 varchar(50)
as
select * from TableName
where TableFieldName1=@Parameter1 and TableFieldName2=@Parameter2

 Note :   where

1. The ProcedureName show the name of the procedure for example ProcCustomerInfo etc

2. The Parameter 1 and parameter 2 are declared the variable and these variables assigned at runtime.

3. The TableName indicates the Name of Table.

4. The TableFieldName1 and TableFieldName2 show the column name(field or attribute name)of TableName

Step2: Now Compile the stored procedure by pressing the f5 (Function key) button. 

It shows the message on the message window as “Command(s) completed successfully.”;

Step3: how to call the stored procedure in our application and passing the parameter.

Write these code on aspx.cs file 

//Connection Created here and making DataReader object dr
SqlCommand cmd = new SqlCommand("ProcedureName",con);//Procedure call in sql command
cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.AddWithValue("@Parameter1",UserValue1);//Set the parameter1 by user input value           
 cmd.Parameters.AddWithValue("@Parameter2", UserValue2); );//Set the parameter2 by user input value 
dr = cmd.ExecuteReader();//Execute the procedure
dr.Read();//Read the first row
//Access the value then close the DataReader and Connection

Insert Query in the stored procedure

Step 1: we write this statement on SQL Server (in query window).

CREATE PROCEDURE [dbo].[ProcedureName] (
@Parameter1 varchar(50), @Parameter2 varchar(100)
AS INSERT INTO TableName VALUES(@Parameter1,@Parameter2)

Step2: Now Compile the stored procedure by pressing the f5 (Function key) button. It shows the message on the message window as “Command(s) completed successfully.”;

Step3: how to call the stored procedure in our application and passing the parameter.

Write these code on aspx.cs file 

SqlCommand command = new  SqlCommand("ProcedureName",con);
command.CommandType = CommandType.StoredProcedure;          
command.Parameters.Add("@Parameter1",  SqlDbType.VarChar).Value = UserInputValue1; //Assign the value
command.Parameters.Add("@Parameter2", SqlDbType.VarChar).Value = UserInputValue2; //Assign the value
command.ExecuteNonQuery();//Execute the procedure return type integer

 
Update Query in the stored procedure

Step 1: we write this statement on SQL Server (in query window).

CREATE PROCEDURE [dbo].[ProcedureName] (
@Parameter1 varchar(50), @Parameter2 varchar(100)
AS UPDATE TableName SET TableFieldName1=@Parameter1 WHERE  TableFieldName2=@Parameter2

Step2: Now Compile the stored procedure by pressing the F5 (Function key) button. It shows the message on the message window as “Command(s) completed successfully.”;

Step3: how to call the stored procedure in our application and passing the parameter.

Write these code on aspx.cs file 

SqlCommand command = new  SqlCommand("ProcedureName",con);
command.CommandType = CommandType.StoredProcedure;          
command.Parameters.Add("@Parameter1",  SqlDbType.VarChar).Value = UserInputValue1; //Assign the value
command.Parameters.Add("@Parameter2", SqlDbType.VarChar).Value = UserInputValue2; //Assign the value
command.ExecuteNonQuery();//Execute the procedure return type integer

  Note: Similarly create for delete  as insert (created above)