SQL - Stored Procedure 📦

SQL is the standard language for the Relational Database, for storing, manipulating and retrieving stored data. For basics of SQL (link)

A Stored Procedure is a SQL code that we can save, so the code can be reused again and again.
So, if we have any SQL query which we write over and over again, we can use it as a stored procedure so that we can just call it to execute it.

The Syntax for creating the stored procedure

Executing stored procedure

Stored Procedure with Parameters

To add Parameters in the procedure, first, we need to alter the procedure for which we can use ALTER procedure statement as follows:

To Execute the above stored procedure, we have to declare a variable for output parameter and specify the parameter as out

Just like this, we can also set multiple parameters by separating each parameter and the data type with a comma

Drop a stored procedure

To drop a stored procedure, use the DROP statement as follows:

DROP PROCEDURE usp_proc;

Thank you!! Feel free to comment on any type of feedback or error you have 😄✌

Comments

[Akash]

nice, short and sweet article. I think it would be tremendously more useful if you include in some ways this feature can be used to optimise or ease development when we are developing a large application (maybe like a shopping site?) and we have multiple entities in out Db like users, products, product_types, roles, permissions etc. There entities are queried many many times by APIs in different ways. Is there some way we can design these procedures that can help multiple developers working on the application utilise such methods instead of writing their own and increase code duplicity?