SQL Stored Procedure
A stored procedure in Structured Query Language is a group of logical statements stored in the database for performing a particular task.
It is a subprogram consisting of a name, a list of parameters, and Transact-SQL statements.
Any user can store the stored procedure as a named object in the SQL database and can call it by using triggers, other procedures, and other programming applications such as Java, PHP, R, C#, Python, etc.
SQL database creates an execution plan and stores it in the cache memory when the stored procedure is called for the first time. The plan is reused by SQL Server, which executes the stored procedure quickly with reliable performance.
Types of Stored Procedure
Following are the two types of Stored Procedures in SQL:
- User-defined Stored Procedures
- System Stored Procedures
User-defined Stored Procedures
User-defined Stored Procedures are created by the database developers and administrators and stored in the current database.
This type of stored procedure provides one or more SQL statements for retrieving, updating, and deleting values from database tables.
User-Defined stored procedure is further categorized into the following two types:
- T-SQL Stored Procedure
- CLR Stored Procedure
T-SQL Stored Procedure
The Transact-SQL procedure accepts the parameters and returns them. This stored procedure manages INSERT, UPDATE, and DELETE statements with or without parameters and gives the row data in the output.
CLR Stored Procedure
CLR stored procedure is that stored procedure which is created by the combination of CLR (Common Language Runtime) and another stored procedure written in a CLR-based language like C# and VB.NET.
CLR procedures are the objects of .Net Framework, which execute in the memory of the SQL database server.
System Stored Procedures
SQL database server creates and executes the system stored procedures for administrative activities. SQL database server does not allow developers to interfere with system stored procedures.
Syntax of Stored Procedure in SQL
The following syntax is used to create the simple stored procedure in Structured Query Language:
The following syntax is used to execute the stored procedure in Structured Query Language:
Example of Stored Procedure in SQL
Firstly, we have to create the table and insert the data into the table in SQL.
The following query creates the Student_Stored_Procedure table using the CREATE TABLE statement:
The following SQL queries insert the record of students into the above table using INSERT INTO statement:
Let’s see the record of the above table using the following SELECT statement:
Student_ID | Student_Name | Student_Course | Student_Age | Student_Marks |
---|---|---|---|---|
101 | Anuj | B.tech | 20 | 88 |
102 | Raman | MCA | 24 | 98 |
104 | Shyam | BBA | 19 | 92 |
107 | Vikash | B.tech | 20 | 78 |
111 | Monu | MBA | 21 | 65 |
114 | Jones | B.tech | 18 | 93 |
121 | Parul | BCA | 20 | 97 |
123 | Divya | B.tech | 21 | 89 |
128 | Hemant | MBA | 23 | 90 |
130 | Nidhi | BBA | 20 | 88 |
132 | Priya | MBA | 22 | 99 |
138 | Mohit | MCA | 21 | 92 |
The following query creates the stored procedure which selects all the records from the above Student_Stored_Procedure table:
Now, execute the stored procedure using the following query to see its output:
Output:
Student_ID | Student_Name | Student_Course | Student_Age | Student_Marks |
---|---|---|---|---|
101 | Anuj | B.tech | 20 | 88 |
102 | Raman | MCA | 24 | 98 |
104 | Shyam | BBA | 19 | 92 |
107 | Vikash | B.tech | 20 | 78 |
111 | Monu | MBA | 21 | 65 |
114 | Jones | B.tech | 18 | 93 |
121 | Parul | BCA | 20 | 97 |
123 | Divya | B.tech | 21 | 89 |
128 | Hemant | MBA | 23 | 90 |
130 | Nidhi | BBA | 20 | 88 |
132 | Priya | MBA | 22 | 99 |
138 | Mohit | MCA | 21 | 92 |
Stored Procedure with One Parameter
The syntax for creating the stored procedure with one parameter is given below:
The syntax for executing the stored procedure with one parameter is given below:
The following query creates the stored procedure which shows the students of a particular course from the above table:
The following query executes the above-stored procedure and shows the record of B.tech students in the output:
Output:
Student_ID | Student_Name | Student_Course | Student_Age | Student_Marks |
---|---|---|---|---|
101 | Anuj | B.tech | 20 | 88 |
107 | Vikash | B.tech | 20 | 78 |
114 | Jones | B.tech | 18 | 93 |
123 | Divya | B.tech | 21 | 89 |
Stored Procedure with Multiple Parameters
The syntax for creating the stored procedure with more than one parameter is given below:
The syntax for executing the stored procedure with multiple parameters is given below:
The following query creates the stored procedure which shows the students of a particular course with a particular age from the above table:
The following query executes the stored procedure and shows the record of those students in the output whose course is B.tech and Age is 20:
Advantages of Stored Procedures in SQL
Following are the important benefits or advantages of stored procedure in Structured Query Language:
- Reduced Network Traffic: The stored procedure minimizes the network traffic between the application and the database server because instead of sending many lines of SQL code, the application only needs to pass the name and parameter of the stored procedure to the database server.
- Stronger Security: The stored procedure provides high security because it restricts the users from accessing the data from the tables directly.
- Reusable: Stored procedure can be used by multiple users without writing the same SQL code repeatedly. Re-usability of the code minimizes the development time.
- Easy to Modify: Any user can easily modify the stored procedure with the help of ALTER TABLE command.
- Increased Performance: Stored Procedure in SQL maximizes the performance of the application.