Basic SQL for Testers : Store Procedures
1. Some Facts about Store Procedure
If a repetitive T-SQL task has to be executed within an application, then the best repository for it is a program called a Stored procedure, stored in SQL Server.
Storing the code inside the SQL Server object gives us many advantages, like:
- Security due to encryption
- Performance gains due to compilation
- Being able to hold the code in a central repository
- Reduction in the amount of data passed over a network by keeping the code on the server
- Hiding the raw data by allowing only stored procedures to gain access to the data
2. Types of Store Procedure
2.1 System Stored Procedures
In SQL Server, many administrative and informational activities can be performed by using system stored procedures. Every time we add or modify a table, make a backup plan, or perform any other administrative function from within Enterprise Manager, we actually call a stored procedure specifically written to complete the desired action. These stored procedures are known as system stored procedures.
2.2 User Stored Procedures
A user stored procedure is any program that is stored and compiled within SQL Server (but not in the master database) and prefixed with sp_. User stored procedures can be categorized into three distinct types:
- User stored procedures
- User defined functions