For an instructor lead, in-depth look at learning SQL click below.
Stored procedures in SQL are pre-compiled and reusable groups of SQL statements that are named and stored in a database. They offer various advantages such as improving performance by reducing the amount of information sent to a server, preventing SQL injection attacks, and more.
Creating a Stored Procedure
Creating a stored procedure involves the “CREATE PROCEDURE” statement followed by the name you’ve chosen for the procedure. The SQL statements that make up the procedure are enclosed in the BEGIN and END statements in the procedure. Here is the basic syntax:
|
1 2 3 4 5 6 7 |
CREATE PROCEDURE procedure_name AS BEGIN --SQL statements END; |
Let’s create a simple stored procedure. Suppose we have a database named ‘EmployeeDB’ with a table ‘EmployeeDetails’ containing details of Employees. We want to create a stored procedure which returns the first name and last name of the employees whose age is over 25.
|
1 2 3 4 5 6 7 8 9 |
CREATE PROCEDURE GetEmployeesOver25 AS BEGIN SELECT FirstName, LastName FROM EmployeeDetails WHERE Age > 25; END; |
Running a Stored Procedure
The stored procedure can be executed using the “EXECUTE” or “EXEC” statement followed by the procedure’s name as follows:
|
1 2 3 |
EXECUTE GetEmployeesOver25; |
Or,
|
1 2 3 |
EXEC GetEmployeesOver25; |
Conclusion
Stored procedures can greatly improve the performance of your database by reducing network traffic and improving security. In this post, we’ve only touched the surface of what you can do with stored procedures. Once you get familiar with basic procedures, you can tackle more advanced features such as input parameters, output parameters, and error handling.