Does one really have to learn SQL to be able to access information in the database? A warehouse managers does not necessarily have to understand SQL or the inner workings of the procedure, if they are interested in knowing the levels of products stored at their warehouse and available for shipment. A software that store database queries is known as the SQL Server Stored Procedures.
Stored Procedures are precompiled database queries, which improve the security, efficiency and usability of database client/server applications. They are used to control transaction management and others run stored procedures inside a transaction in order to see transparency on them.
The stored procedures are no different from the procedures used in other programming languages. Stored procedures have the following characteristics:
1. It accepts input parameters and returns multiple values in the form of output parameters to the calling procedure or batch.
2. It contains programming statements that perform operations in the Error! Hyperlink reference not valid., including other calling procedures.
3. It returns a status value to a calling procedure or batch to indicate success or failure. The reason for such failure is stated.
Some of the benefits that stored procedures are:
1. Database queries are pre-compiled.
2. It reduces client and server traffic. Stored procedures reduce the long SQL queries to a single line, which transmitted over the wire.
3. The code is reusable bringing multiple users and client programs to use the stored procedures.
4. It enhances security controls. Users can execute a stored procedure independently once permission is granted.