Menu
When using prepared statements inside stored procedures, should they be deallocated at the end of the procedure or not, or does it not matter, and why?
Some code to explain:
CREATE PROCEDURE getCompanyByName (IN name VARCHAR(100))
NOT DETERMINISTIC
BEGIN
PREPARE gcbnStatement FROM 'SELECT * FROM Companies WHERE name=? LIMIT 1';
SET @companyName = name;
EXECUTE gcbnStatement USING @companyName;
DEALLOCATE PREPARE gcbnStatement;
END $$
So - should the DEALLOCATE statement be there or not? Cheers!
/Victor
623
We can use the prepared statements in a stored procedure by writing it inside the BEGIN and END block. We can understand it by creating an example that returns all records from a table by passing the table's name as a parameter of the stored procedure.
Prepared Statements: Why use prepared statements? There are numerous advantages to using prepared statements in your applications, both for security and performance reasons. Prepared statements can help increase security by separating SQL logic from the data being supplied.
Query result obtained by SELECT type prepared statement is stored in query cache on a universal basis independent of the source type of query and can be reused both by another execution of this or another prepared statement and by direct query when its text is identical to the SQL text of the first prepared statement.
DEALLOCATE is used to deallocate a previously prepared SQL statement. If you do not explicitly deallocate a prepared statement, it is deallocated when the session ends. For more information on prepared statements, see PREPARE.
According to the MySQL docs:
A prepared statement is specific to the session in which it was created. If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically.
So, no, I wouldn't bother doing it explicitly, unless you have very long-running sessions.
170
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
