CODEWITHSUNDEEP
mysqlstored-proceduresprepared-statement
Victor
Victor

Reputation: 4239

Should prepared statements be deallocated when used inside stored procedures?

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

Upvotes: 9

Views: 10260

Answers (3)

Kudehinbu Oluwaponle
Kudehinbu Oluwaponle

Reputation: 1145

Deallocating Prepared statements in stored routines is best practice.

Prepared statements are global to a session and are not automatically deallocated when a stored routine ends.

If you have too many created but not deallocated prepared statement, you might encounter/exceed the max_prepared_stmt_count limit

Upvotes: 0

Chris Lampley
Chris Lampley

Reputation:

If you use connection pooling, it is definitely a good idea to deallocate them.

Upvotes: 1

skaffman
skaffman

Reputation: 403551

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.

Upvotes: 9

Related Questions