I couldn't understand importance of stored procedures

Question

Today i learned how to create stored procedures in MySQL.

DELIMETER //
CREATE PROCEDURE new(IN first INT)
BEGIN 
    SELECT * FROM table WHERE first_id= first;
END //

Lets say i created this and called it from PHP; CALL new('4'); or i wrote my sql command directly into PHP

$mysqli->query("SELECT * FROM table WHERE first_id= $first");

I have 20 sql commands in my PHP and i am doing them copy past into phpmyadmin as stored procedures. they are not descreasing. Now what i gain from stored procedure? Just a few letter? or??? Thanks for help.

If your asking what the point is to making a stored procedure is it's to reduce having to copy and paste code. much the same reason why you move repated code in any langue into a function. say you have a 100 line block of query code which makes temp tables, processes data, loops, etc. and this is repeated 5 times in your pogram. what if you find a bug in it or go to make it run better? you have to alter all 5 instances of it to be the exact same, with a Stored Procedure you only have to do it in one spot to update all 5 instances — Memor-X, Nov 26 '15 at 23:08
Possible duplicate of [What is a Stored Procedure?](http://stackoverflow.com/questions/459457/what-is-a-stored-procedure) — Ben N, Nov 26 '15 at 23:12
a real world example of doing it in programming is wrapping up every mysql PHP function you use in it's own function and calling that. in the begining you may have been using `mysql_*` function but now that it's depreciated you need to upgrade to `mysqli_*` or `PDO`. how many times do you think you call your mysql query function? want to update every last one of those and risk missing one? or just edit your wrapper function once to affect them all — Memor-X, Nov 26 '15 at 23:13
@BenN no i red it. It does not answer my question. My question is how should i use SP. — Webber Depor, Nov 26 '15 at 23:15
@WebberDepor if all 20 are completely different and are doing separate things then it's probably to maintain consistency in the code and future-proofing it because there's bound to be a day where one of these will be repeated. think about my `mysql_` example, back then did anyone think that `mysql_*` would get deprecated and removed? and keep in mind that `mysqli_*` doesn't just have an extra letter in it's name as non OO functions of it have changed the order of arguments — Memor-X, Nov 26 '15 at 23:36
Same benefits as functions as noted already: attraction, named block of code, central location, consistent interface. Usually easier to change. Changeable on the server. Changeable after distribution of binaries. Security independent of base table permission. Pre-compilation. Less bandwidth and no implementation details to sniff over the network... — shawnt00, Nov 27 '15 at 03:02

score 0 · Answer 1 · answered Nov 26 '15 at 23:17

Stored procedures are a good way for building a clean abstraction layer between what is going on in the database and your server side logic. In addition a stored procedure allows that you specify exactly what kind of data you are expexting (data type and possibly length) which can help to improve security in your application (at least at this "access data from database"-point).

I couldn't understand importance of stored procedures

1 Answers1