In this article I will explain you about stored procedure.
How to create, execute and alter stored procedure.
Why to use stored procedures and advantages of stored procedures.
-
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.
-
Stored Procedures are coding block in database server. It is pre compiled entity i.e. it is compiled at once and can be used again and again.
-
With help of stored procedure a group of SQL statements can be executed sequentially.
-
To supply data to the procedure we must have to use parameters in procedure.
-
Stored procedures use parameters mapping concept.
-
In parameter mapping front end and procedure parameters names, type and direction must be same and where front-end parameter length should be less than or equal to procedure parameter length (than only can map parameters).
-
To return any value from procedure we use return statement.
How to create a stored procedure
create procedure insertData
(@RollNo int,
@Name varchar(50),
@Fees float)
as
begin
insert into student values(@RollNo,@Name,@Fees)
select * from student
end
How to execute a stored procedure
exec insertData 8, 'Mahesh', 5600
Output of above stored procedure
Modifying a Stored Procedure
alter procedure insertData
(@RollNo int,
@Name varchar(50),
@Fees float)
as
begin
insert into student values(@RollNo,@Name,@Fees)
select * from student
end
Why to use stored procedures?
You normally write SQL statements, like select, inserts, updates to access your data from database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure.
Every time you write a query it is parsed in database. If you have written a stored procedure for it, it will be parsed once and can be executed N number of times.
Stored procedures can also improve performance. All the conditional logic and is written into a stored procedure which is a single execution block on the database server.
Advantages of stored procedure
|
Modular programming |
Stored Procedures are coding block in database server. IT is pre compiled entity i.e. it is compiled at once and can be used again and again. |
|
Performance |
Stored procedures provide faster code execution and reduce network traffic.
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 optimize 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. |
|
Security |
Users can execute a stored procedure without needing to execute any of the statements directly.
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. |
Conclusion
Hope this article might have helped you in understanding stored procedures.
Your feedback and constructive contributions are welcome. Please feel free to contact me for feedback or comments you may have about this article.