A stored procedure is a set of SQL commands that has 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. Stored procedures improve performance by reducing network traffic and CPU load.

A stored procedure is more than prepared SQL code that you save so you can reuse the code over and over again.  So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.

In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.

Benefits of Stored Procedure

·    You can write a stored procedure once, and then call it from multiple places in your application.

·    Faster execution: Stored procedures are parsed and optimized as soon as they are created and the stored procedure is stored in memory. This means that it will execute a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimze your SQL code every time it runs.

·   Reduced network traffic: If you send many lines of SQL code over the network to your SQL Server, this will impact on network performance. This is especially true if you have hundreds of lines of SQL code and/or you have lots of activity on your application. Running the code on the SQL Server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.

·    Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way.

Syntax:

CREATE PROCEDURE <procedure name>
(
@parameter name datatype(size),
@parameter name datatype(size)
)
AS
BEGIN
//Select,Insert,Update,Delete commands 
END 

Example

CREATE PROCEDURE sp_sampleInsert
(
@v1 varchar(50),
@v2 varchar(50),
@v3 varchar(50)
)
AS
BEGIN
 insert into sample(value1,value2,value3) values(@v1,@v2,@v3)
END

Modifying Stored Procedure

ALTER PROCEDURE sp_sampleInsert
(
@v1 varchar(50),
@v2 varchar(50)
)
AS
BEGIN
 insert into sample(value1,value2) values(@v1,@v2)
END

Drop Stored Procedure

Drop PROCEDURE <procedure name>

Executing Stored Procedure

EXEC <procedure name>