SQL: Beginner Guide to Store Procedures (Part 1/2)

•

SQL: Beginner Guide to Store Procedures a thread... (Part 1/2)

1. What is Store Procedure? It's a group of SQL statements stored on the DB side, to perform some action (business logic) or perform any DB-related task. SP may or may not return output and may or may not take arguments, they are optional.

2. How to Define It? Below, we've created a store procedure with the name "GetEmp" which is simply querying an EMPLOYEE_TABLE and fetching all employees. Between BEGIN and END, we've got the body where we write all SQL statements & business logic. (snippets are for MySQL) Eg:

3. How to Call it? In order to call the above procedure, we use the "CALL" keyword. (snippets are for MySQL) Eg: The below snippet will call the above-created SP.

4. Types of Parameters in SP: IN: Using it, caller can pass param. OUT: Output sent by SP to caller. INOUT: The caller can pass the value inside SP, and SP can modify it and give it back to the caller with an updated value. (in part 2, we'll cover this in detail)

5. Benefits of SP? - Gives good performance, as it's precompiled in nature. - Gets updated instantly without any deployment/server restart very useful when fixing production issues without any downtime.

- Reusability, as it can be used by multiple users/clients and also it accepts parameters so the same code can be used for multiple purposes. - Reduces network traffic, as several SQL operations can be performed with a single step and save network round trips.

6. Drawbacks of SP: - Errors are detected at runtime. - Difficult to debug an SP. - Difficult to maintain especially if it carries business logic.

That's it for now! In part 2, we will see how to create store procedures with different types of parameters. If you liked this then you might also like my other thread on SQL: twitter.com/vikasrajputin/status/1535072762288410624

I hope you learned something new! Every Mon, Wed & Fri - I write a thread on Fullstack Development. Follow @vikasrajputin to read all my future threads.