Stored Procedure in SQL Server
In this blog, I’m trying to explain the stored procedure in
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
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
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.
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.
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.
insert into sample(value1,value2,value3) values(@v1,@v2,@v3)
insert into sample(value1,value2) values(@v1,@v2)
Drop PROCEDURE <procedure name>
EXEC <procedure name>