Stored Procedure
Set pre-compiled SQL statement. That are Stored in Database.
Multiple user can share the single Procedure.
Multiple user can share the single Procedure.
What is a stored procedure?
Stored Procedure is a function which contains
collection of SQL Queries. Procedure can take inputs, process them and send
back output.
What are the advantages a stored procedure?
Stored Procedures are precompiled and stored
in database. This enables the database to execute the queries much faster.
Since many queries can be included in a stored procedure, round trip time t o
execute multiple queries from source code to database and back is avoided.
Create table in sql:
create table employee(name varchar(20),desgination varchar(40),address varchar(max))
Create procedure in sql:
create procedure sp_employe(@name
varchar(20),@desgination varchar(40),@address varchar(max))
as
begin
insert into employee values(@name,@desgination,@address)
end
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. If you utilize
them in a planned manner, you'll find the development cycle takes less time.
4
Enhanced security
controls. You can grant users permission
to execute a stored procedure independently of underlying table
permissions.