Stored Procedures are a batch of SQL statements that can be executed in a couple of ways. stored procedures are used to make the back end logic easy. To create a stored procedure the syntax is fairly simple:
first write the keyword "create" followed by the keyword "procedure" and followed by the stored procedure name.
And then "As Begin" followed by the syntax and at the last the keyword "last". Below is the example of a simple stored procedure.
CREATE PROCEDURE procedurename
Select * from tblname
The above is a very simple stored procedure in which we are selecting just values from table. using stored procedure we can pass the parameter also and perform any CRUD operations on tables/views.A benefit of stored procedures is that you can centralize data access logic into a single place that is then easy for DBA's to optimize.
Now suppose if you need to retrieve any particular parameter from database table using stored procedure then follow this-
Create PROCEDURE sp_getparameter
@itemid INT --define input parameter, the one which you want to retrieve
SELECT itemid+' '+itemname FROM tblname WHERE itemid=@itemtid
Advantages of using stored procedures
1.Stored procedure allows modular programming.
2.Stored Procedure allows faster execution.
3.Stored Procedure can reduce network traffic.
4.Stored procedures provide better security to your data. There are much more advantage, once you will start working with this you will find !!