Until four months ago, I had only studied what a stored procedure in SQL was but never had a chance to actually use the technique. Fortunately, while working on a recent project, I was able to create several stored procedures and enjoy manipulating them.
For those who are new to this SQL concept, let me take a few steps back and explain what a Stored procedure in SQL is.
A stored procedure is a named group of SQL statements that can be stored in the database so that it can be reused and shared in the application. Stored procedures can access and modify the database.
Now defined, let's talk about the benefits of a stored procedure:
Creating a simple stored procedure
We will create a simple procedure that will provide the details of a user from the Users table containing name, address, age, phone, and email. This assumes that you know how to create a table and insert values.
CREATE PROCEDURE GetUserDetails(IN userdId INT)
AS
BEGIN
SELECT u.name, u.address, u.age, u.phone, u.email from Users as u WHERE u.id=userId;
END
We have created a stored procedure, but how do we use it? We can call it in the following manner:
CALL GetUserDetails(34);
This will give us the details of the user which we want. We have stored the code in the database, and we can simply call the procedure by passing the user id without writing the whole query again.
Conclusion
Stored procedures are a secure and safe way to give access to your database. They can be saved for reuse as often as needed. We hope you try it out and that it will help you better understand what stored procedures are. Keep Learning!