DEV Community

Cover image for SQL - Stored Procedure 📦
Sharanjeet kaur
Sharanjeet kaur

Posted on

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

Image description

Executing stored procedure

Image description

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:

Image description

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

Image description

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 😄✌

Top comments (1)

Collapse
 
zyabxwcd profile image
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?