MySQL Questions

Thor

Honorary Master
Joined
Jun 5, 2014
Messages
41,849
Just a general thread for questions related to MySQL.

Here is my question, when should one use stored procedures?

I use PHP with PDO and almost always have a dynamic where clause, is it wrong to use prepared statements throughout the app instead of stored procedures, how do you even make use of a dynamic query in a stored procedure?

As an example is a query like this possible as a stored procedure:
PHP:
$stmt = $dbh->prepare("SELECT * FROM users WHERE firstname = :name OR lastname = :lastname");
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindParam(':lastname', $lastname, PDO::PARAM_STR);
 
Last edited:

netcruiser

Well-Known Member
Joined
Sep 21, 2012
Messages
395
You should use stored procedures as much as you can. For many reasons, from maintainability to readability to performance.
 

Batista

Executive Member
Joined
Sep 2, 2011
Messages
7,909
Welcome back dude.

I go by how often the procedure needs to change, if its often then its a SP, if not then its hardcoded into the app.
 

Thor

Honorary Master
Joined
Jun 5, 2014
Messages
41,849
You should use stored procedures as much as you can. For many reasons, from maintainability to readability to performance.
Alright, so then how do you call a MySQL stored procedure in PDO when there's a WHERE clause?
 

netcruiser

Well-Known Member
Joined
Sep 21, 2012
Messages
395
Alright, so then how do you call a MySQL stored procedure in PDO when there's a WHERE clause?

I don't know the exact MySQL syntax, but in SQL Server you'd do something like this for your example:

CREATE PROCEDURE GetUserInfo @firstname varchar, @lastname varchar
AS
SELECT *
FROM users
WHERE FirstName = @firstname AND LastName = @lastname

then just call/prepare it (however you do in php) with a statement:

EXEC GetUserInfo 'first name' 'last name'
 

Thor

Honorary Master
Joined
Jun 5, 2014
Messages
41,849
I don't know the exact MySQL syntax, but in SQL Server you'd do something like this for your example:

CREATE PROCEDURE GetUserInfo @firstname varchar, @lastname varchar
AS
SELECT *
FROM users
WHERE FirstName = @firstname AND LastName = @lastname

then just call/prepare it (however you do in php) with a statement:

EXEC GetUserInfo 'first name' 'last name'
This is the MySQL specific thread.
You will need to confirm if the above works in MySQL then I'll add it as solution/demonstration to the OP.
 

Spacerat

Expert Member
Joined
Jul 29, 2015
Messages
1,036
I don't know the exact MySQL syntax, but in SQL Server you'd do something like this for your example:

CREATE PROCEDURE GetUserInfo @firstname varchar, @lastname varchar
AS
SELECT *
FROM users
WHERE FirstName = @firstname AND LastName = @lastname

then just call/prepare it (however you do in php) with a statement:

EXEC GetUserInfo 'first name' 'last name'

I rarely use SPs.To be honest, using an SP like the above provides no benefit whatsoever. It is just a hassle
 

_kabal_

Expert Member
Joined
Oct 24, 2005
Messages
3,418
The only place I have used stored procs is while consulting at large financial corporates

I see no reason to use them if that is an option, especially in this day of micro services
 

^^vampire^^

Expert Member
Joined
Feb 17, 2009
Messages
3,829
For me stored procs are for large sections of db crunching, usually reporting or something combining a lot of data together where you don't want someone just changing it for the hell of it.

Like _kabal_ said I've only found this important in large corps where lots of people touch the code.
 

poffle

Executive Member
Joined
Apr 21, 2007
Messages
5,468
For simple selects no need for a stored procedure. If you're doing complex queries with many Joins / sub queries then it's better to use a SP, faster processing as the work is done on the DB server as well as the SPs can be cached for faster execution the next time.

Dunno PHP but just make sure your queries are parametized.. SQL injections aren't lekker.
 

Spacerat

Expert Member
Joined
Jul 29, 2015
Messages
1,036
For me stored procs are for large sections of db crunching, usually reporting or something combining a lot of data together
code.
Agreed

For simple selects no need for a stored procedure. If you're doing complex queries with many Joins / sub queries then it's better to use a SP, faster processing as the work is done on the DB server as well as the SPs can be cached for faster execution the next time.

Dunno PHP but just make sure your queries are parametized.. SQL injections aren't lekker.

No need for SPs even for complex selects. As long as the query can be expressed as a single sql statement (a single statement can get pretty big when you start to use CTEs, correlated subqueries etc) there is no need for a SP. If the SQL is parameterised, the query plan will get cached anyway. And the DB engine still does all the work. When you are talking about queries that need to generate temp tables etc, typically for BI type stuff then yes you need a SP.

And for bulk data manipulation yes sure you need SP. But not for typical CRUD stuff.
 
Top