Using Stored Procedure in SQL
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:
- Ease of Use – We can use it by calling the stored procedure name and passing parameters, if any.
- Performance – Upon the first use, a plan for the stored procedure is created and stored in the buffer pool for subsequent quick execution.
- Security – Stored procedures reduce the threat by eliminating direct access to the tables.
- Reusable – It can be executed by multiple users or multiple client applications without the need to rewrite the code.
- Low Network traffic – The server only passes the procedure name instead of the whole query, reducing network traffic.
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!