In a database management system (DBMS), a stored procedure is a set of Structured Query Language (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).

Benefits of Stored Procedures:

1) Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the
    execution plan. This results in tremendous performance boosts when stored procedures are called
    repeatedly.
2) Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be
     happy to learn that stored procedures can reduce long SQL queries to a single line that is
     transmitted over the wire.
3) Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple    
     users and client programs.
4) If you utilize them in a planned manner, you'll find the development cycle takes less time.
5) Enhanced security controls.You can grant users permission to execute a stored procedure
    independently of underlying table permissions.

Create:

CREATE procedure procedurename
as

sql statement

Execute:

EXEC  ProcedureName

Rename:

EXEC sp_rename 'Current_sp_name' 'New_sp_name';

Delete:

DROP procedure dbo.SPNAME