Menu
I'm looking into stored procedures at the moment.
According to this article (page 8) in the dev section of the mysql website...
Ordinarily, it's not normal to put SELECT statements in stored procedures, this is for illustration. I decided that some procedure should simply select from our table, so that when you call the procedure it will be obvious that it's working.
Why is that?
Is using Stored-procedures to simplify complicated select statements not 'best-practices'?
What are some specific situations where it is beneficial to use a stored procedure? Example?
663
We can not directly use stored procedures in a SELECT statement.
In MySQL, it is not possible to use select from procedure in FROM clause. You can use CALL command and after that the SELECT statement can be executed. Here is the query to display records from the table using select statement after calling stored procedure.
Each procedure has one or more statements. In our case, these are SQL statements. So, you can write a procedure that will – insert new data, update or delete existing, retrieve data using the SELECT statement. And even better, you can combine more (different statements) in the stored procedures.
SQL prepared statements ( PREPARE , EXECUTE , DEALLOCATE PREPARE ) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).
Generally stored procedures are intended for complex processing in the database. There are debates raging about their benefits. I never saw that SELECTs in a stored procedure was a bad thing but I wouldn't expect that every single SQL statement that has to be written goes into a stored procedure either. It should be reserved for those processing that involve multiple statements and will have to be performed repeatedly.
Jeff has a rant about them here.
To answer your direct question for specific examples, I have found that I avoid them because of the portability issue. I attempt to do all my processing application side. At the same time I do not have to worry about network bandwidth in my application so each situation is different.
66
A specific situation where it is beneficial to use Stored Procedure/Routines is that it can provide error checking on parameters similar to functions in OO paradigm. It gives added 'Encapsulation'
A simple example:
CREATE PROCEDURE select_table(IN @id INT)
BEGIN
IF @id < O THEN
-- ERROR! do something here
ELSEIF
SELECT * from TABLE WHERE id = @id;
END IF
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
