MySQL Questions

[Thor]

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:

$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);

 

[OCP]

Welcome back!

 

[netcruiser]

You should use stored procedures as much as you can. For many reasons, from maintainability to readability to performance.

 

[Batista]

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]

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]

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]

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.

 

[netcruiser]

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.

Of course MySQL supports stored procedures syntax is almost exactly the same:

http://www.mysqltutorial.org/stored-procedures-parameters.aspx

 

[Spacerat]

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_]

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^^]

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]

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]

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.